

<!DOCTYPE html>
<html lang="zh-CN" data-default-color-scheme=auto>



<head>
  <meta charset="UTF-8">
  <link rel="apple-touch-icon" sizes="76x76" href="/img/favicon.png">
  <link rel="icon" href="/img/favicon.png">
  <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=5.0, shrink-to-fit=no">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  
  <meta name="theme-color" content="#2f4154">
  <meta name="description" content="Blue~u~u~u">
  <meta name="author" content="Blue~u~u~u">
  <meta name="keywords" content="">
  <meta name="description" content="SQL 优化第一节 准备数据1、修改配置为了能够看出 SQL 优化前后的性能差异，我们需要较为大量的数据。生成这些数据我们需要用到 MySQL 中的自定义函数。 但是 MySQL 默认关闭了自定义函数功能，所以我们需要通过修改配置文件来开启这项功能。 12# 使用 vim 编辑器打开配置文件vim &#x2F;etc&#x2F;my.cnf  在配置文件末尾增加如下内容： 12# 设置为 1 表示开启这项功能log_">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL-SQL优化">
<meta property="og:url" content="http://www.slx.blue/2022/03/14/MySQL-SQL%E4%BC%98%E5%8C%96/index.html">
<meta property="og:site_name" content="Blue~u~u~u~u">
<meta property="og:description" content="SQL 优化第一节 准备数据1、修改配置为了能够看出 SQL 优化前后的性能差异，我们需要较为大量的数据。生成这些数据我们需要用到 MySQL 中的自定义函数。 但是 MySQL 默认关闭了自定义函数功能，所以我们需要通过修改配置文件来开启这项功能。 12# 使用 vim 编辑器打开配置文件vim &#x2F;etc&#x2F;my.cnf  在配置文件末尾增加如下内容： 12# 设置为 1 表示开启这项功能log_">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL21.png?versionId=CAEQKRiBgIDLjpf7.xciIDRjNWVlMTgwMWQxZDQwYmZiMmM1NGQwMTA3MDM0M2E0">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL22.png?versionId=CAEQKRiBgICXjZf7.xciIGQwNmVlZjdlMDQ4ODQ0ZjlhMDNlYTU2NWYxYTY5OWI0">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL24.png?versionId=CAEQKRiBgICKjZf7.xciIGQ4ZjdiN2ZjYzk0NDQ2ZGE4NmU3YmRlMTMzYTlhMDUy">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL25.png?versionId=CAEQKRiBgMCAjZf7.xciIGQ3YjFhNWM5ZGZkNTQ2Y2RiN2UxZmRmNDhkMzAxMDkx">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL26.png?versionId=CAEQKRiBgMD3jJf7.xciIDY2NTYwNDQ2NmU2ODQ4YWViYzc4ZTE4MmFhOGJhZWVm">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL62.png">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL27.png?versionId=CAEQKRiCgID2jJf7.xciIGY1MDFhNzEwZmEyOTRmM2M4NDA2ZDlkMTRjOTkwODQ2">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL28.png?versionId=CAEQKRiBgID0jJf7.xciIDk3OTM0NjAwYTA5YzQwY2I4YTFlMWE1MzZmNDJjYTZi">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL29.png?versionId=CAEQKRiBgMDyjJf7.xciIGE5MGNlYTE1NTc2YzQ4OTdhMTM1MGNkY2I5NjIwNmY5">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL30.png?versionId=CAEQKRiBgIDsjJf7.xciIGY1NDI1YmMwNzRhZjQxYTk5ZWY5ZmY0NmE1NTBiOWE1">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL31.png?versionId=CAEQKRiBgIDwjJf7.xciIDA5NzJlODFiODhjZDQwYTNiZjRlNmViNDA1ZjZlMTc1">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL32.png?versionId=CAEQKRiBgMC2i5f7.xciIGEyMDE2M2Y3YWZmMzQ1NzM4YTI1YzUwZjU5YTkxNzQw">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL33.png?versionId=CAEQKRiBgICzi5f7.xciIDk4ZDAwNTI3NGM5NjQ5ZDI5OGM0NTcwZGNiMGZiZmUy">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL34.png?versionId=CAEQKRiBgMC0i5f7.xciIDZlMDNjNmM5NDk1MDQ0OGI4M2UyZTI5MGJmMzdhMGI4">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL35.png?versionId=CAEQKRiBgICxi5f7.xciIGVlYjdiMjg5YjExZTRmM2Y4NzhiNGJjMDY5M2E1MjU3">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL36.png?versionId=CAEQKRiBgMCgi5f7.xciIDQxMzhmM2JlYWNmNTQ1NzM5NzMxOThjOTc1MmRmY2M2">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL37.png?versionId=CAEQKRiBgMCfi5f7.xciIDExNWFlODY0YjMwMDRkZmE4NDUyODgwNjUxZDBiMTA2">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL38.png?versionId=CAEQKRiBgICei5f7.xciIDRmNzdiZjM3YTk5ZDRkNDJhMzMyMmU3NzgxNzAxOGYy">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL39.png?versionId=CAEQKRiBgICci5f7.xciIGQzNzBmYmEwZmI4YzRmY2Q5ZDUxNWI4NjE5MTVjZWU1">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL40.png?versionId=CAEQKRiBgICbi5f7.xciIDFhYTkxMDk4MGQzOTQ0MGJiZGJmMWU3MTc1M2Y2MTI0">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL41.png?versionId=CAEQKRiBgICfi5f7.xciIDgzNDIzZjhmNmYyOTRjODFhNjBlMTM1ZjZjYmYyZDcx">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL42.png?versionId=CAEQKRiBgMDjiZf7.xciIDdmOTgyMmMyZjdmMDRjNTE5MTU2ZmIxMTVhZGE3YjQ2">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL43.png?versionId=CAEQKRiBgIDhiZf7.xciIDliMGY1NTA2YTk4OTRjZWRhZjM3Nzk0ODEzNGRhZTNh">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL44.png?versionId=CAEQKRiBgIDfiZf7.xciIDZjNTMxMWYwNDVjNzRhMTA5NmI2NzZjYzVhZWExMDRl">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL45.png?versionId=CAEQKRiBgIDdiZf7.xciIDc3YWUyOGFlNzBlNzRlNjQ5Njg4Y2U5OTI2ZWNjNzQ0">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL46.png?versionId=CAEQKRiDgMDMiZf7.xciIDMxNzFmOTU1MTlmNjQ0OGVhYTQzNmUwZTUzNWU4ZWU2">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL47.png?versionId=CAEQKRiBgMDMiZf7.xciIDIyYmJiZmJkN2E4NjRlOThhNTVhNmM4MTYzMmM0YjNi">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL48.png?versionId=CAEQKRiBgIDLiZf7.xciIDViZDViZDAyMDAxODRjOGViZTkyZWVlMThjZTY1ZDk2">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL49.png?versionId=CAEQKRiBgIDJiZf7.xciIDZlYzhkMmNmMWNiZTQ1ZjU4YTI1NzQ0MTg3ZDgyZDgw">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL50.png?versionId=CAEQKRiBgIDIiZf7.xciIDllNzk5NzFmNTUxODQ1NTU4NjdhMmIxYzE4MDljMTE4">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL51.png?versionId=CAEQKRiBgMDIiZf7.xciIDE5MWNmNDYxMGYzYTQ3OTFiZmNmYTg5YWRlOGE3Njhk">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL52.png?versionId=CAEQKRiCgMCSiJf7.xciIDg1N2E5NmZhODE2MDQwNjA5NWU5MWMzYjg0ZmQ5Njg0">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL53.png?versionId=CAEQKRiBgMCRiJf7.xciIDkxZGJhN2JjZTU3YzQ1MThiNWJmZGJkNjJiZTczMzc3">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL54.png?versionId=CAEQKRiBgMCPiJf7.xciIGU4NTc3MjM1ZGIzNjRlOTNhMzI1Y2FjMGI3NGIyMTZj">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL55.png?versionId=CAEQKRiBgICLiJf7.xciIGUzZGRlZDI4NGRjMjQ1NjM5NDEwN2IwODdjZGU3YjMw">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL56.png?versionId=CAEQKRiBgID3h5f7.xciIGQxZWY2ZmEwZWJhMDRhNzU5YzAyMjc1ZmNhYzU3MmNh">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL57.png?versionId=CAEQKRiBgMDyh5f7.xciIDJhNmNjMTJhMjY5NTQzODc5ZWVkNTVhMDJjMDEzNGUx">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL58.png?versionId=CAEQKRiBgMD5h5f7.xciIGI5NGE1ZDlkNWI1NjQzMzdhNzFmOTI0NzdjNWY1OTYx">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL59.png?versionId=CAEQKRiBgMD3h5f7.xciIDA0OTI4ZjQxOTJmMzRlOTM5MmViOTg5NGEwNTNmNTM1">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL60.png?versionId=CAEQKRiCgMD5h5f7.xciIGJlZjJiMjM5ZGIxODQ0NzVhMjhlYzg4MDIwMWY3Zjk5">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL61.png?versionId=CAEQKRiBgIDth5f7.xciIDg0NmNiNmRiNGMxMDQwNmU4MTllNThkNWNlMGEzNjhl">
<meta property="og:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL23.png?versionId=CAEQKRiBgICLjZf7.xciIDQxYzBkMzMyM2ZlNjQ2YmNhNzQ5OGMyZGQ1NzA5OGVj">
<meta property="article:published_time" content="2022-03-14T11:12:18.000Z">
<meta property="article:modified_time" content="2022-03-14T11:14:42.826Z">
<meta property="article:author" content="Blue~u~u~u">
<meta property="article:tag" content="mysql">
<meta name="twitter:card" content="summary_large_image">
<meta name="twitter:image" content="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL21.png?versionId=CAEQKRiBgIDLjpf7.xciIDRjNWVlMTgwMWQxZDQwYmZiMmM1NGQwMTA3MDM0M2E0">
  
  <title>MySQL-SQL优化 - Blue~u~u~u~u</title>

  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4/dist/css/bootstrap.min.css" />


  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/github-markdown-css@4/github-markdown.min.css" />
  <link  rel="stylesheet" href="/lib/hint/hint.min.css" />

  
    
    
      
      <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/highlight.js@10/styles/github-gist.min.css" />
    
  

  
    <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3/dist/jquery.fancybox.min.css" />
  


<!-- 主题依赖的图标库，不要自行修改 -->

<link rel="stylesheet" href="//at.alicdn.com/t/font_1749284_ba1fz6golrf.css">



<link rel="stylesheet" href="//at.alicdn.com/t/font_1736178_kmeydafke9r.css">


<link  rel="stylesheet" href="/css/main.css" />

<!-- 自定义样式保持在最底部 -->


  <script id="fluid-configs">
    var Fluid = window.Fluid || {};
    var CONFIG = {"hostname":"www.slx.blue","root":"/","version":"1.8.12","typing":{"enable":true,"typeSpeed":180,"cursorChar":"_","loop":true},"anchorjs":{"enable":true,"element":"h1,h2,h3,h4,h5,h6","placement":"right","visible":"hover","icon":""},"progressbar":{"enable":true,"height_px":3,"color":"#29d","options":{"showSpinner":false,"trickleSpeed":100}},"copy_btn":true,"image_zoom":{"enable":true,"img_url_replace":["",""]},"toc":{"enable":true,"headingSelector":"h1,h2,h3,h4,h5,h6","collapseDepth":0},"lazyload":{"enable":true,"loading_img":"/img/loading.gif","onlypost":false,"offset_factor":2},"web_analytics":{"enable":false,"baidu":null,"google":null,"gtag":null,"tencent":{"sid":null,"cid":null},"woyaola":null,"cnzz":null,"leancloud":{"app_id":null,"app_key":null,"server_url":null,"path":"window.location.pathname"}},"search_path":"/local-search.xml"};
  </script>
  <script  src="/js/utils.js" ></script>
  <script  src="/js/color-schema.js" ></script>
<meta name="generator" content="Hexo 5.4.0"></head>

<script src="https://cdn.jsdelivr.net/npm/jquery/dist/jquery.min.js"></script>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/font-awesome/css/font-awesome.min.css"/>
<script src="/live2d-widget/autoload.js"></script>


<body>
  <header style="height: 70vh;">
    <nav id="navbar" class="navbar fixed-top  navbar-expand-lg navbar-dark scrolling-navbar">
  <div class="container">
    <a class="navbar-brand" href="/">
      <strong>Blue~u~u</strong>
    </a>

    <button id="navbar-toggler-btn" class="navbar-toggler" type="button" data-toggle="collapse"
            data-target="#navbarSupportedContent"
            aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
      <div class="animated-icon"><span></span><span></span><span></span></div>
    </button>

    <!-- Collapsible content -->
    <div class="collapse navbar-collapse" id="navbarSupportedContent">
      <ul class="navbar-nav ml-auto text-center">
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/">
                <i class="iconfont icon-home-fill"></i>
                首页
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/archives/">
                <i class="iconfont icon-archive-fill"></i>
                归档
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/categories/">
                <i class="iconfont icon-category-fill"></i>
                分类
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/tags/">
                <i class="iconfont icon-tags-fill"></i>
                标签
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/about/">
                <i class="iconfont icon-user-fill"></i>
                关于
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/links/">
                <i class="iconfont icon-link-fill"></i>
                友链
              </a>
            </li>
          
        
        
          <li class="nav-item" id="search-btn">
            <a class="nav-link" target="_self" href="javascript:;" data-toggle="modal" data-target="#modalSearch" aria-label="Search">
              &nbsp;<i class="iconfont icon-search"></i>&nbsp;
            </a>
          </li>
        
        
          <li class="nav-item" id="color-toggle-btn">
            <a class="nav-link" target="_self" href="javascript:;" aria-label="Color Toggle">&nbsp;<i
                class="iconfont icon-dark" id="color-toggle-icon"></i>&nbsp;</a>
          </li>
        
      </ul>
    </div>
  </div>
</nav>

    <div class="banner" id="banner" parallax=true
         style="background: url('/images/default.png') no-repeat center center;
           background-size: cover;">
      <div class="full-bg-img">
        <div class="mask flex-center" style="background-color: rgba(0, 0, 0, 0.3)">
          <div class="page-header text-center fade-in-up">
            <span class="h2" id="subtitle" title="MySQL-SQL优化">
              
            </span>

            
              <div class="mt-3">
  
  
    <span class="post-meta">
      <i class="iconfont icon-date-fill" aria-hidden="true"></i>
      <time datetime="2022-03-14 19:12" pubdate>
        2022年3月14日 晚上
      </time>
    </span>
  
</div>

<div class="mt-1">
  
    <span class="post-meta mr-2">
      <i class="iconfont icon-chart"></i>
      25k 字
    </span>
  

  
    <span class="post-meta mr-2">
      <i class="iconfont icon-clock-fill"></i>
      
      
      78 分钟
    </span>
  

  
  
    
      <!-- 不蒜子统计文章PV -->
      <span id="busuanzi_container_page_pv" style="display: none">
        <i class="iconfont icon-eye" aria-hidden="true"></i>
        <span id="busuanzi_value_page_pv"></span> 次
      </span>
    
  
</div>

            
          </div>

          
        </div>
      </div>
    </div>
  </header>

  <main>
    
      

<div class="container-fluid nopadding-x">
  <div class="row nomargin-x">
    <div class="d-none d-lg-block col-lg-2"></div>
    <div class="col-lg-8 nopadding-x-md">
      <div class="container nopadding-x-md" id="board-ctn">
        <div class="py-5" id="board">
          <article class="post-content mx-auto">
            <!-- SEO header -->
            <h1 style="display: none">MySQL-SQL优化</h1>
            
              <p class="note note-info">
                
                  本文最后更新于：2 个月前
                
              </p>
            
            <div class="markdown-body">
              <h1 id="SQL-优化"><a href="#SQL-优化" class="headerlink" title="SQL 优化"></a>SQL 优化</h1><h1 id="第一节-准备数据"><a href="#第一节-准备数据" class="headerlink" title="第一节 准备数据"></a>第一节 准备数据</h1><h2 id="1、修改配置"><a href="#1、修改配置" class="headerlink" title="1、修改配置"></a>1、修改配置</h2><p>为了能够看出 SQL 优化前后的性能差异，我们需要较为大量的数据。生成这些数据我们需要用到 MySQL 中的自定义函数。</p>
<p>但是 MySQL 默认关闭了自定义函数功能，所以我们需要通过修改配置文件来开启这项功能。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"># 使用 vim 编辑器打开配置文件<br>vim <span class="hljs-operator">/</span>etc<span class="hljs-operator">/</span>my.cnf<br></code></pre></td></tr></table></figure>

<p>在配置文件末尾增加如下内容：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"># 设置为 <span class="hljs-number">1</span> 表示开启这项功能<br>log_bin_trust_function_creators<span class="hljs-operator">=</span><span class="hljs-number">1</span><br></code></pre></td></tr></table></figure>

<p>然后重启 MySQL 服务：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql">systemctl restart mysqld.service<br></code></pre></td></tr></table></figure>

<h2 id="2、执行-SQL-语句生成数据"><a href="#2、执行-SQL-语句生成数据" class="headerlink" title="2、执行 SQL 语句生成数据"></a>2、执行 SQL 语句生成数据</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br></pre></td><td class="code"><pre><code class="hljs sql"># 创建数据库<br><span class="hljs-keyword">create</span> database db_hr_sys;<br><br># 使用数据库<br>use db_hr_sys;<br><br># 创建数据库表：部门表<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> `dept` (<br> `id` <span class="hljs-type">INT</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> AUTO_INCREMENT,<br> `deptName` <span class="hljs-type">VARCHAR</span>(<span class="hljs-number">30</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">NULL</span>,<br> `address` <span class="hljs-type">VARCHAR</span>(<span class="hljs-number">40</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">NULL</span>,<br> `ceo` <span class="hljs-type">INT</span> <span class="hljs-keyword">NULL</span> ,<br> <span class="hljs-keyword">PRIMARY</span> KEY (`id`)<br>) ENGINE<span class="hljs-operator">=</span>INNODB AUTO_INCREMENT<span class="hljs-operator">=</span><span class="hljs-number">1</span> <span class="hljs-keyword">DEFAULT</span> CHARSET<span class="hljs-operator">=</span>utf8;<br><br># 创建数据库表：员工表<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> `emp` (<br> `id` <span class="hljs-type">INT</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> AUTO_INCREMENT,<br> `empno` <span class="hljs-type">INT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> ,<br> `name` <span class="hljs-type">VARCHAR</span>(<span class="hljs-number">20</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">NULL</span>,<br> `age` <span class="hljs-type">INT</span>(<span class="hljs-number">3</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">NULL</span>,<br> `deptId` <span class="hljs-type">INT</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">NULL</span>,<br> <span class="hljs-keyword">PRIMARY</span> KEY (`id`)<br> #<span class="hljs-keyword">CONSTRAINT</span> `fk_dept_id` <span class="hljs-keyword">FOREIGN</span> KEY (`deptId`) <span class="hljs-keyword">REFERENCES</span> `t_dept` (`id`)<br>) ENGINE<span class="hljs-operator">=</span>INNODB AUTO_INCREMENT<span class="hljs-operator">=</span><span class="hljs-number">1</span> <span class="hljs-keyword">DEFAULT</span> CHARSET<span class="hljs-operator">=</span>utf8;<br><br># 声明函数：生成随机字符串<br>DELIMITER $$<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">FUNCTION</span> rand_string(n <span class="hljs-type">INT</span>) <span class="hljs-keyword">RETURNS</span> <span class="hljs-type">VARCHAR</span>(<span class="hljs-number">255</span>)<br><span class="hljs-keyword">BEGIN</span>    <br><span class="hljs-keyword">DECLARE</span> chars_str <span class="hljs-type">VARCHAR</span>(<span class="hljs-number">100</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">&#x27;abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ&#x27;</span>;<br><span class="hljs-keyword">DECLARE</span> return_str <span class="hljs-type">VARCHAR</span>(<span class="hljs-number">255</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">&#x27;&#x27;</span>;<br><span class="hljs-keyword">DECLARE</span> i <span class="hljs-type">INT</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-number">0</span>;<br>WHILE i <span class="hljs-operator">&lt;</span> n DO  <br><span class="hljs-keyword">SET</span> return_str <span class="hljs-operator">=</span>CONCAT(return_str,<span class="hljs-built_in">SUBSTRING</span>(chars_str,<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span><span class="hljs-operator">+</span>RAND()<span class="hljs-operator">*</span><span class="hljs-number">52</span>),<span class="hljs-number">1</span>));  <br><span class="hljs-keyword">SET</span> i <span class="hljs-operator">=</span> i <span class="hljs-operator">+</span> <span class="hljs-number">1</span>;<br><span class="hljs-keyword">END</span> WHILE;<br><span class="hljs-keyword">RETURN</span> return_str;<br><span class="hljs-keyword">END</span> $$<br><br># 声明函数：生成随机数字<br>DELIMITER $$<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">FUNCTION</span>  rand_num (from_num <span class="hljs-type">INT</span> ,to_num <span class="hljs-type">INT</span>) <span class="hljs-keyword">RETURNS</span> <span class="hljs-type">INT</span>(<span class="hljs-number">11</span>)<br><span class="hljs-keyword">BEGIN</span>   <br><span class="hljs-keyword">DECLARE</span> i <span class="hljs-type">INT</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-number">0</span>;  <br><span class="hljs-keyword">SET</span> i <span class="hljs-operator">=</span> <span class="hljs-built_in">FLOOR</span>(from_num <span class="hljs-operator">+</span>RAND()<span class="hljs-operator">*</span>(to_num <span class="hljs-operator">-</span> from_num<span class="hljs-operator">+</span><span class="hljs-number">1</span>))   ;<br><span class="hljs-keyword">RETURN</span> i;  <br><span class="hljs-keyword">END</span>$$<br><br># 创建存储过程：插入员工数据<br>DELIMITER $$<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">PROCEDURE</span>  insert_emp(  <span class="hljs-keyword">START</span> <span class="hljs-type">INT</span> ,  max_num <span class="hljs-type">INT</span> )<br><span class="hljs-keyword">BEGIN</span>  <br><span class="hljs-keyword">DECLARE</span> i <span class="hljs-type">INT</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-number">0</span>;   <br> <span class="hljs-keyword">SET</span> autocommit <span class="hljs-operator">=</span> <span class="hljs-number">0</span>;    #设置手动提交事务<br> REPEAT  #循环<br> <span class="hljs-keyword">SET</span> i <span class="hljs-operator">=</span> i <span class="hljs-operator">+</span> <span class="hljs-number">1</span>;  #赋值<br> <span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> emp (empno, NAME ,age ,deptid ) <span class="hljs-keyword">VALUES</span> ((<span class="hljs-keyword">START</span><span class="hljs-operator">+</span>i),rand_string(<span class="hljs-number">6</span>),rand_num(<span class="hljs-number">30</span>,<span class="hljs-number">50</span>),rand_num(<span class="hljs-number">1</span>,<span class="hljs-number">10000</span>));  <br> UNTIL i <span class="hljs-operator">=</span> max_num  <br> <span class="hljs-keyword">END</span> REPEAT;  <br> <span class="hljs-keyword">COMMIT</span>;  #提交事务<br><span class="hljs-keyword">END</span>$$<br><br># 创建存储过程：插入部门数据<br>DELIMITER $$<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">PROCEDURE</span> `insert_dept`(  max_num <span class="hljs-type">INT</span> )<br><span class="hljs-keyword">BEGIN</span>  <br><span class="hljs-keyword">DECLARE</span> i <span class="hljs-type">INT</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-number">0</span>;   <br> <span class="hljs-keyword">SET</span> autocommit <span class="hljs-operator">=</span> <span class="hljs-number">0</span>;    <br> REPEAT  <br> <span class="hljs-keyword">SET</span> i <span class="hljs-operator">=</span> i <span class="hljs-operator">+</span> <span class="hljs-number">1</span>;  <br> <span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> dept ( deptname,address,ceo ) <span class="hljs-keyword">VALUES</span> (rand_string(<span class="hljs-number">8</span>),rand_string(<span class="hljs-number">10</span>),rand_num(<span class="hljs-number">1</span>,<span class="hljs-number">500000</span>));  <br> UNTIL i <span class="hljs-operator">=</span> max_num  <br> <span class="hljs-keyword">END</span> REPEAT;  <br> <span class="hljs-keyword">COMMIT</span>; <br><span class="hljs-keyword">END</span>$$<br><br># 调用存储过程，向部门表插入<span class="hljs-number">1</span>万条数据<br><span class="hljs-keyword">CALL</span> insert_dept(<span class="hljs-number">10000</span>);<br><br># 调用存储过程，向员工表插入<span class="hljs-number">50</span>万条数据<br><span class="hljs-keyword">CALL</span> insert_emp(<span class="hljs-number">100000</span>,<span class="hljs-number">500000</span>);<br></code></pre></td></tr></table></figure>

<h1 id="第二节-慢查询日志和视图"><a href="#第二节-慢查询日志和视图" class="headerlink" title="第二节 慢查询日志和视图"></a>第二节 慢查询日志和视图</h1><h2 id="1、需求"><a href="#1、需求" class="headerlink" title="1、需求"></a>1、需求</h2><p>在实际开发和项目运行的过程中，需要尽量准确的把查询时间消耗较大的 SQL 语句给找出来。然后有针对性的建立索引，再使用 explain 技术进行分析，找到性能瓶颈，最后调整 SQL 语句。</p>
<h2 id="2、慢查询日志介绍"><a href="#2、慢查询日志介绍" class="headerlink" title="2、慢查询日志介绍"></a>2、慢查询日志介绍</h2><p>由 MySQL 负责以日志的形式记录那些执行时间超过阈值的 SQL 语句。当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。</p>
<h2 id="3、查看及开启"><a href="#3、查看及开启" class="headerlink" title="3、查看及开启"></a>3、查看及开启</h2><p>慢查询日志记录功能默认关闭。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql">#默认情况下slow_query_log的值为OFF<br><span class="hljs-keyword">SHOW</span> VARIABLES <span class="hljs-keyword">LIKE</span> <span class="hljs-string">&#x27;%slow_query_log%&#x27;</span>;  <br></code></pre></td></tr></table></figure>

<p>命令行开启：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">set</span> <span class="hljs-keyword">global</span> slow_query_log<span class="hljs-operator">=</span><span class="hljs-number">1</span>; <br></code></pre></td></tr></table></figure>

<p>慢查询日志记录long_query_time时间</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SHOW</span> VARIABLES <span class="hljs-keyword">LIKE</span> <span class="hljs-string">&#x27;%long_query_time%&#x27;</span>;<br><span class="hljs-keyword">SHOW</span> <span class="hljs-keyword">GLOBAL</span> VARIABLES <span class="hljs-keyword">LIKE</span> <span class="hljs-string">&#x27;long_query_time&#x27;</span>;<br><span class="hljs-keyword">SET</span> <span class="hljs-keyword">GLOBAL</span> long_query_time<span class="hljs-operator">=</span><span class="hljs-number">0.1</span>; <br></code></pre></td></tr></table></figure>

<p><strong>注意: 运行时间正好等于long_query_time的情况，并不会被记录下来。</strong></p>
<p>永久生效</p>
<figure class="highlight properties"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><code class="hljs properties"><span class="hljs-meta">修改my.cnf文件，[mysqld]下增加或修改参数slow_query_log</span> <span class="hljs-string">和slow_query_log_file后，然后重启MySQL服务器。也即将如下四行配置进my.cnf文件 </span><br><span class="hljs-attr">slow_query_log</span>=<span class="hljs-string">1</span><br><span class="hljs-attr">slow_query_log_file</span>=<span class="hljs-string">/var/lib/mysql/atguigu-slow.log </span><br><span class="hljs-attr">long_query_time</span>=<span class="hljs-string">3</span><br><span class="hljs-attr">log_output</span>=<span class="hljs-string">FILE</span><br></code></pre></td></tr></table></figure>



<h2 id="4、查看捕获到的记录"><a href="#4、查看捕获到的记录" class="headerlink" title="4、查看捕获到的记录"></a>4、查看捕获到的记录</h2><h3 id="①执行一条慢-SQL"><a href="#①执行一条慢-SQL" class="headerlink" title="①执行一条慢 SQL"></a>①执行一条慢 SQL</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-keyword">DISTINCT</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> emp <span class="hljs-keyword">UNION</span> (<span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> emp) <span class="hljs-keyword">UNION</span> (<span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> emp)<br></code></pre></td></tr></table></figure>



<h3 id="②查询当前系统中有多少条慢查询记录"><a href="#②查询当前系统中有多少条慢查询记录" class="headerlink" title="②查询当前系统中有多少条慢查询记录"></a>②查询当前系统中有多少条慢查询记录</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SHOW</span> <span class="hljs-keyword">GLOBAL</span> STATUS <span class="hljs-keyword">LIKE</span> <span class="hljs-string">&#x27;%Slow_queries%&#x27;</span>; <br></code></pre></td></tr></table></figure>



<p>显示结果是：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL21.png?versionId=CAEQKRiBgIDLjpf7.xciIDRjNWVlMTgwMWQxZDQwYmZiMmM1NGQwMTA3MDM0M2E0" srcset="/img/loading.gif" lazyload alt="images"></p>
<h3 id="③查看日志信息内容"><a href="#③查看日志信息内容" class="headerlink" title="③查看日志信息内容"></a>③查看日志信息内容</h3><p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL22.png?versionId=CAEQKRiBgICXjZf7.xciIGQwNmVlZjdlMDQ4ODQ0ZjlhMDNlYTU2NWYxYTY5OWI0" srcset="/img/loading.gif" lazyload alt="img"></p>
<h3 id="④使用mysqldumpslow命令导出"><a href="#④使用mysqldumpslow命令导出" class="headerlink" title="④使用mysqldumpslow命令导出"></a>④使用mysqldumpslow命令导出</h3><p>在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。</p>
<ul>
<li>-a: 不将数字抽象成N，字符串抽象成S</li>
<li>-s: 是表示按照何种方式排序；</li>
<li>c: 访问次数</li>
<li>l: 锁定时间</li>
<li>r: 返回记录</li>
<li>t: 查询时间</li>
<li>al:平均锁定时间</li>
<li>ar:平均返回记录数</li>
<li>at:平均查询时间</li>
<li>-t: 即为返回前面多少条的数据；</li>
<li>-g: 后边搭配一个正则匹配模式，大小写不敏感的；</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><code class="hljs sql">#得到返回记录集最多的<span class="hljs-number">10</span>个<span class="hljs-keyword">SQL</span><br>mysqldumpslow <span class="hljs-operator">-</span>s r <span class="hljs-operator">-</span>t <span class="hljs-number">10</span> <span class="hljs-operator">/</span>var<span class="hljs-operator">/</span>lib<span class="hljs-operator">/</span>mysql<span class="hljs-operator">/</span>atguigu<span class="hljs-operator">-</span>slow.log<br><br>#得到访问次数最多的<span class="hljs-number">10</span>个<span class="hljs-keyword">SQL</span><br>mysqldumpslow <span class="hljs-operator">-</span>s c <span class="hljs-operator">-</span>t <span class="hljs-number">10</span> <span class="hljs-operator">/</span>var<span class="hljs-operator">/</span>lib<span class="hljs-operator">/</span>mysql<span class="hljs-operator">/</span>atguigu<span class="hljs-operator">-</span>slow.log<br><br>#得到按照时间排序的前<span class="hljs-number">10</span>条里面含有左连接的查询语句<br>mysqldumpslow <span class="hljs-operator">-</span>s t <span class="hljs-operator">-</span>t <span class="hljs-number">10</span> <span class="hljs-operator">-</span>g &quot;left join&quot; <span class="hljs-operator">/</span>var<span class="hljs-operator">/</span>lib<span class="hljs-operator">/</span>mysql<span class="hljs-operator">/</span>atguigu<span class="hljs-operator">-</span>slow.log<br><br>#另外建议在使用这些命令时结合 <span class="hljs-operator">|</span> 和more 使用 ，否则有可能出现爆屏情况<br>mysqldumpslow <span class="hljs-operator">-</span>s r <span class="hljs-operator">-</span>t <span class="hljs-number">10</span> <span class="hljs-operator">/</span>var<span class="hljs-operator">/</span>lib<span class="hljs-operator">/</span>mysql<span class="hljs-operator">/</span>atguigu<span class="hljs-operator">-</span>slow.log <span class="hljs-operator">|</span> more<br></code></pre></td></tr></table></figure>

<h1 id="第三节-Explain-分析"><a href="#第三节-Explain-分析" class="headerlink" title="第三节 Explain 分析"></a>第三节 Explain 分析</h1><h2 id="1、概念"><a href="#1、概念" class="headerlink" title="1、概念"></a>1、概念</h2><h3 id="①MySQL-内优化器"><a href="#①MySQL-内优化器" class="headerlink" title="①MySQL 内优化器"></a>①MySQL 内优化器</h3><p>MySQL 体系结构中，包含 SQL 解析器、优化器等组件。SQL 解析器解析 SQL 之后，生成解析树。经过验证，解析树正确后，由优化器进一步优化解析树。</p>
<h3 id="②Explain-分析"><a href="#②Explain-分析" class="headerlink" title="②Explain 分析"></a>②Explain 分析</h3><p>使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。 分析你的查询语句或是表结构的性能瓶颈。</p>
<table>
<thead>
<tr>
<th>列名</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td><code>id</code></td>
<td>在一个大的查询语句中每个SELECT关键字都对应一个<code>唯一的id</code></td>
</tr>
<tr>
<td><code>select_type</code></td>
<td>SELECT关键字对应的那个查询的类型</td>
</tr>
<tr>
<td><code>table</code></td>
<td>表名</td>
</tr>
<tr>
<td><code>partitions</code></td>
<td>匹配的分区信息</td>
</tr>
<tr>
<td><code>type</code></td>
<td>针对单表的访问方法</td>
</tr>
<tr>
<td><code>possible_keys</code></td>
<td>可能用到的索引</td>
</tr>
<tr>
<td><code>key</code></td>
<td>实际上使用的索引</td>
</tr>
<tr>
<td><code>key_len</code></td>
<td>实际使用到的索引长度</td>
</tr>
<tr>
<td><code>ref</code></td>
<td>当使用索引列等值查询时，与索引列进行等值匹配的对象信息</td>
</tr>
<tr>
<td><code>rows</code></td>
<td>预估的需要读取的记录条数</td>
</tr>
<tr>
<td><code>filtered</code></td>
<td>某个表经过搜索条件过滤后剩余记录条数的百分比</td>
</tr>
<tr>
<td><code>Extra</code></td>
<td>一些额外的信息</td>
</tr>
</tbody></table>
<h2 id="2、准备测试数据"><a href="#2、准备测试数据" class="headerlink" title="2、准备测试数据"></a>2、准备测试数据</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">create</span> database db_hr;<br><br>use db_hr;<br><br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> t1<br>(<br>    id      <span class="hljs-type">INT</span>(<span class="hljs-number">10</span>) AUTO_INCREMENT,<br>    content <span class="hljs-type">VARCHAR</span>(<span class="hljs-number">100</span>) <span class="hljs-keyword">NULL</span>,<br>    <span class="hljs-keyword">PRIMARY</span> KEY (id)<br>);<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> t2<br>(<br>    id      <span class="hljs-type">INT</span>(<span class="hljs-number">10</span>) AUTO_INCREMENT,<br>    content <span class="hljs-type">VARCHAR</span>(<span class="hljs-number">100</span>) <span class="hljs-keyword">NULL</span>,<br>    <span class="hljs-keyword">PRIMARY</span> KEY (id)<br>);<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> t3<br>(<br>    id      <span class="hljs-type">INT</span>(<span class="hljs-number">10</span>) AUTO_INCREMENT,<br>    content <span class="hljs-type">VARCHAR</span>(<span class="hljs-number">100</span>) <span class="hljs-keyword">NULL</span>,<br>    <span class="hljs-keyword">PRIMARY</span> KEY (id)<br>);<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> t4<br>(<br>    id      <span class="hljs-type">INT</span>(<span class="hljs-number">10</span>) AUTO_INCREMENT,<br>    content <span class="hljs-type">VARCHAR</span>(<span class="hljs-number">100</span>) <span class="hljs-keyword">NULL</span>,<br>    <span class="hljs-keyword">PRIMARY</span> KEY (id)<br>);<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t1(content)<br><span class="hljs-keyword">VALUES</span> (CONCAT(<span class="hljs-string">&#x27;t1_&#x27;</span>, <span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> RAND() <span class="hljs-operator">*</span> <span class="hljs-number">1000</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t2(content)<br><span class="hljs-keyword">VALUES</span> (CONCAT(<span class="hljs-string">&#x27;t2_&#x27;</span>, <span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> RAND() <span class="hljs-operator">*</span> <span class="hljs-number">1000</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t3(content)<br><span class="hljs-keyword">VALUES</span> (CONCAT(<span class="hljs-string">&#x27;t3_&#x27;</span>, <span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> RAND() <span class="hljs-operator">*</span> <span class="hljs-number">1000</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t4(content)<br><span class="hljs-keyword">VALUES</span> (CONCAT(<span class="hljs-string">&#x27;t4_&#x27;</span>, <span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> RAND() <span class="hljs-operator">*</span> <span class="hljs-number">1000</span>)));<br></code></pre></td></tr></table></figure>

<h2 id="3、字段"><a href="#3、字段" class="headerlink" title="3、字段"></a>3、字段</h2><h3 id="3、id-字段"><a href="#3、id-字段" class="headerlink" title="3、id 字段"></a>3、id 字段</h3><h4 id="①id-字段含义"><a href="#①id-字段含义" class="headerlink" title="①id 字段含义"></a>①id 字段含义</h4><ul>
<li>一个 select 对应一个 id 唯一的值</li>
<li>id 值每出现一次，就代表 执行SQL语句的一个大步骤</li>
<li>总体的 SQL 语句执行的『大步骤』越少越好</li>
<li>多个 id 值<ul>
<li>id 值相同：代表是小步骤。按照从上到下的顺序依次执行</li>
<li>id 值不同：代表有多个大步骤，大步骤按照id从大到小顺序执行</li>
</ul>
</li>
</ul>
<h4 id="②举例"><a href="#②举例" class="headerlink" title="②举例"></a>②举例</h4><h5 id="1-id-值相同的情况"><a href="#1-id-值相同的情况" class="headerlink" title="[1]id 值相同的情况"></a>[1]id 值相同的情况</h5><p>SQL 本身：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> t1.id,t2.id,t3.id,t4.id <span class="hljs-keyword">from</span> t1,t2,t3,t4<br></code></pre></td></tr></table></figure>



<p>应用 Explain 分析：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql">explain <span class="hljs-keyword">select</span> t1.id,t2.id,t3.id,t4.id <span class="hljs-keyword">from</span> t1,t2,t3,t4<br></code></pre></td></tr></table></figure>

<p>执行结果：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL24.png?versionId=CAEQKRiBgICKjZf7.xciIGQ4ZjdiN2ZjYzk0NDQ2ZGE4NmU3YmRlMTMzYTlhMDUy" srcset="/img/loading.gif" lazyload alt="images"></p>
<h5 id="2-id-值不同的情况"><a href="#2-id-值不同的情况" class="headerlink" title="[2]id 值不同的情况"></a>[2]id 值不同的情况</h5><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><code class="hljs sql">EXPLAIN<br><span class="hljs-keyword">SELECT</span> t1.id<br><span class="hljs-keyword">FROM</span> t1<br><span class="hljs-keyword">WHERE</span> t1.id <span class="hljs-operator">=</span> (<span class="hljs-keyword">SELECT</span> t2.id <span class="hljs-keyword">FROM</span> t2 <span class="hljs-keyword">WHERE</span> t2.id <span class="hljs-operator">=</span> (<span class="hljs-keyword">SELECT</span> t3.id <span class="hljs-keyword">FROM</span> t3 <span class="hljs-keyword">WHERE</span> t3.content <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;t3_354&#x27;</span>))<br></code></pre></td></tr></table></figure>



<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL25.png?versionId=CAEQKRiBgMCAjZf7.xciIGQ3YjFhNWM5ZGZkNTQ2Y2RiN2UxZmRmNDhkMzAxMDkx" srcset="/img/loading.gif" lazyload alt="images"></p>
<h5 id="3-相同的-id-值和不同的-id-值都有"><a href="#3-相同的-id-值和不同的-id-值都有" class="headerlink" title="[3]相同的 id 值和不同的 id 值都有"></a>[3]相同的 id 值和不同的 id 值都有</h5><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><code class="hljs sql">EXPLAIN<br><span class="hljs-keyword">SELECT</span> t1.id, (<span class="hljs-keyword">select</span> t4.id <span class="hljs-keyword">from</span> t4 <span class="hljs-keyword">where</span> t4.id <span class="hljs-operator">=</span> t1.id) id4<br><span class="hljs-keyword">FROM</span> t1,<br>     t2<br></code></pre></td></tr></table></figure>



<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL26.png?versionId=CAEQKRiBgMD3jJf7.xciIDY2NTYwNDQ2NmU2ODQ4YWViYzc4ZTE4MmFhOGJhZWVm" srcset="/img/loading.gif" lazyload alt="images"></p>
<h5 id="4-有子查询但是-id-值相同"><a href="#4-有子查询但是-id-值相同" class="headerlink" title="[4]有子查询但是 id 值相同"></a>[4]有子查询但是 id 值相同</h5><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><code class="hljs sql">explain<br><span class="hljs-keyword">select</span> t1.id<br><span class="hljs-keyword">from</span> t1<br><span class="hljs-keyword">where</span> t1.id <span class="hljs-keyword">in</span> (<span class="hljs-keyword">select</span> t2.id <span class="hljs-keyword">from</span> t2);<br><br></code></pre></td></tr></table></figure>



<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL62.png" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>这是因为查询优化器将子查询转换为了连接查询。</p>
<h3 id="4、select-type-字段"><a href="#4、select-type-字段" class="headerlink" title="4、select_type 字段"></a>4、select_type 字段</h3><h4 id="①字段含义介绍"><a href="#①字段含义介绍" class="headerlink" title="①字段含义介绍"></a>①字段含义介绍</h4><p>一条 SQL 语句总体来看：其中可能会包含很多个 select 关键字。每一个 select代表整个SQL语句执行计划中的一次小的查询，而每一个 select 关键字的每一次查询都有可能是不同类型的查询。</p>
<p>select_type 字段就是用来描述每一个 select 关键字的查询类型，意思是我们只要知道了某个小查询的<code>select_type属性</code>，就知道了这个小查询在整个大查询中<strong>扮演</strong>了一个什么<strong>角色</strong>。而通过查看各个小查询部分扮演的角色，我们可以了解到整体 SQL 语句的结构，从而判断当前 SQL 语句的结构是否存在问题。</p>
<h4 id="②介绍字段取值含义"><a href="#②介绍字段取值含义" class="headerlink" title="②介绍字段取值含义"></a>②介绍字段取值含义</h4><table>
<thead>
<tr>
<th>取值</th>
<th>含义</th>
</tr>
</thead>
<tbody><tr>
<td>SIMPLE</td>
<td>简单的 select 查询，查询中不包含子查询或者 UNION</td>
</tr>
<tr>
<td>PRIMARY</td>
<td>查询中若包含任何复杂的子部分，最外层查询则被标记为 primary</td>
</tr>
<tr>
<td>SUBQUERY</td>
<td>在 SELECT 或 WHERE 列表中包含了子查询</td>
</tr>
<tr>
<td>DEPENDENT SUBQUERY</td>
<td>在 SELECT 或 WHERE 列表中包含了子查询，子查询基于外层</td>
</tr>
<tr>
<td>UNCACHEABLE SUBQUREY</td>
<td>表示这个 subquery 的查询要受到外部表查询的影响</td>
</tr>
<tr>
<td>DERIVED</td>
<td>在 FROM 列表中包含的子查询被标记为 DERIVED(衍生)。MySQL 会递归执行这些子查询，把结果放在临时表里</td>
</tr>
<tr>
<td>UNION</td>
<td>这是 UNION 语句其中的一个 SQL 元素</td>
</tr>
<tr>
<td>UNION RESULT</td>
<td>从 UNION 表获取结果的 SELECT，也就是在 UNION 合并查询结果的基础上，不使用全部字段，选取一部分字段。</td>
</tr>
</tbody></table>
<p>具体分析如下：</p>
<ul>
<li><p><code>SIMPLE</code></p>
<p>查询语句中不包含<code>UNION</code>、不包含子查询的查询都算作是<code>SIMPLE</code>类型，比方说下边这个单表查询的<code>select_type</code>的值就是<code>SIMPLE</code>：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql">mysql<span class="hljs-operator">&gt;</span> EXPLAIN <span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> s1;<br><br></code></pre></td></tr></table></figure>

<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL27.png?versionId=CAEQKRiCgID2jJf7.xciIGY1MDFhNzEwZmEyOTRmM2M4NDA2ZDlkMTRjOTkwODQ2" srcset="/img/loading.gif" lazyload alt="image-20210810213211336"></p>
<p>当然，连接查询也算是<code>SIMPLE</code>类型，比如：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql">mysql<span class="hljs-operator">&gt;</span> EXPLAIN <span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> s1 <span class="hljs-keyword">INNER</span> <span class="hljs-keyword">JOIN</span> s2;<br><br></code></pre></td></tr></table></figure>



<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL28.png?versionId=CAEQKRiBgID0jJf7.xciIDk3OTM0NjAwYTA5YzQwY2I4YTFlMWE1MzZmNDJjYTZi" srcset="/img/loading.gif" lazyload alt="image-20210810213232889"></p>
</li>
<li><p><code>PRIMARY</code></p>
<p>对于包含<code>UNION</code>、<code>UNION ALL</code>或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的<code>select_type</code>值就是<code>PRIMARY</code>，比方说：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql">mysql<span class="hljs-operator">&gt;</span> EXPLAIN <span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> s1 <span class="hljs-keyword">UNION</span> <span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> s2;<br><br></code></pre></td></tr></table></figure>



<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL29.png?versionId=CAEQKRiBgMDyjJf7.xciIGE5MGNlYTE1NTc2YzQ4OTdhMTM1MGNkY2I5NjIwNmY5" srcset="/img/loading.gif" lazyload alt="image-20210810213252095"></p>
<p>从结果中可以看到，最左边的小查询<code>SELECT * FROM s1</code>对应的是执行计划中的第一条记录，它的<code>select_type</code>值就是<code>PRIMARY</code>。</p>
</li>
<li><p><code>UNION</code></p>
<p>对于包含<code>UNION</code>或者<code>UNION ALL</code>的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的<code>select_type</code>值就是<code>UNION</code>，可以对比上一个例子的效果，这就不多举例子了。</p>
</li>
<li><p><code>UNION RESULT</code></p>
<p><code>MySQL</code>选择使用临时表来完成<code>UNION</code>查询的去重工作，针对该临时表的查询的<code>select_type</code>就是<code>UNION RESULT</code>，例子上边有。</p>
</li>
<li><p><code>SUBQUERY</code></p>
<p>如果包含子查询的查询语句不能够转为对应的<code>semi-join</code>的形式（不用管什么是 semi-join，只需要知道这是进一步优化），并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个<code>SELECT</code>关键字代表的那个查询的<code>select_type</code>就是<code>SUBQUERY</code>，比如下边这个查询：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql">mysql<span class="hljs-operator">&gt;</span> EXPLAIN <span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> s1 <span class="hljs-keyword">WHERE</span> key1 <span class="hljs-keyword">IN</span> (<span class="hljs-keyword">SELECT</span> key1 <span class="hljs-keyword">FROM</span> s2) <span class="hljs-keyword">OR</span> key3 <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;a&#x27;</span>;<br><br></code></pre></td></tr></table></figure>



<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL30.png?versionId=CAEQKRiBgIDsjJf7.xciIGY1NDI1YmMwNzRhZjQxYTk5ZWY5ZmY0NmE1NTBiOWE1" srcset="/img/loading.gif" lazyload alt="image-20210810213311371"></p>
<p>可以看到，外层查询的<code>select_type</code>就是<code>PRIMARY</code>，子查询的<code>select_type</code>就是<code>SUBQUERY</code>。需要大家注意的是，由于 select_type 为 SUBQUERY 的子查询会被物化（将子查询结果作为一个临时表来加快查询执行速度），所以只需要执行一遍。</p>
</li>
<li><p><code>DEPENDENT SUBQUERY</code></p>
<p>如果整体 SQL 语句执行的顺序是：</p>
<ul>
<li>先执行外层查询</li>
<li>再执行内层子查询</li>
<li>然后外层查询结果中的每一条再去匹配内层子查询结果的每一条</li>
</ul>
<p>这样，内外两层的查询结果就是<strong>相乘的关系</strong>。相乘就有可能导致总的查询操作次数非常大。所以经过 explain 分析后，如果发现查询类型是 DEPENDENT SUBQUERY 就需要引起各位注意了——这是一个<strong>危险</strong>的信号，通常是需要修复的一个问题！</p>
<p>当然，就实际工作中来说：别说 DEPENDENT SUBQUERY，就连 SUBQUERY 都不应该出现。</p>
</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><code class="hljs sql">EXPLAIN<br><span class="hljs-keyword">SELECT</span> t1.id, (<span class="hljs-keyword">select</span> t4.id <span class="hljs-keyword">from</span> t4 <span class="hljs-keyword">where</span> t4.id <span class="hljs-operator">=</span> t1.id) id4<br><span class="hljs-keyword">FROM</span> t1,<br>     t2;<br><br></code></pre></td></tr></table></figure>



<h3 id="5、table-字段"><a href="#5、table-字段" class="headerlink" title="5、table 字段"></a>5、table 字段</h3><p>显示当前这一步查询操作所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是别名。不论我们的查询语句有多复杂，里边儿<code>包含了多少个表</code>，到最后也是需要对每个表进行<code>单表访问</code>的，所以 MySQL 规定<strong>EXPLAIN语句输出的每条记录都对应着某个单表的访问方法</strong>，该条记录的table列代表着该表的表名。</p>
<h3 id="6、partitions-字段"><a href="#6、partitions-字段" class="headerlink" title="6、partitions 字段"></a>6、partitions 字段</h3><p>代表分区表中的命中情况。如果是非分区表，该项为 null。逻辑上是一个整体的数据，可以在物理层保存时，拆分成很多个分片。分片在分区中保存。数据分片的好处是：</p>
<ul>
<li>方便在很多个不同分区之间方便的移动分片，分摊负载，弹性扩容。</li>
<li>给主分片创建复制分片可以增强数据安全性。</li>
</ul>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL31.png?versionId=CAEQKRiBgIDwjJf7.xciIDA5NzJlODFiODhjZDQwYTNiZjRlNmViNDA1ZjZlMTc1" srcset="/img/loading.gif" lazyload alt="images"></p>
<h3 id="7、type-字段-重要"><a href="#7、type-字段-重要" class="headerlink" title="7、type 字段 [重要]"></a>7、type 字段 [重要]</h3><p>对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。具体取值参见下表（从上到下，性能越来越好）：</p>
<table>
<thead>
<tr>
<th>取值</th>
<th>含义</th>
</tr>
</thead>
<tbody><tr>
<td>ALL</td>
<td>全表扫描</td>
</tr>
<tr>
<td>index</td>
<td>在索引表（聚簇索引、非聚簇索引都算）中做全表扫描</td>
</tr>
<tr>
<td>range</td>
<td>在一定范围内查询索引表</td>
</tr>
<tr>
<td>ref</td>
<td>通过普通的二级索引列与常量进行等值匹配时来查询某个表</td>
</tr>
<tr>
<td>eq_ref</td>
<td>在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是<code>eq_ref</code></td>
</tr>
<tr>
<td>const</td>
<td>根据主键或者唯一二级索引列与常数进行等值匹配</td>
</tr>
<tr>
<td>system</td>
<td>表仅有一行记录，这是const类型的特例，查询起来非常迅速</td>
</tr>
<tr>
<td>null</td>
<td>MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。</td>
</tr>
</tbody></table>
<p>在上述查询方式中，从 eq_ref 开始，条件就很苛刻了，不容易达到。所以实际开发时要求，<strong>至少能达到 range 水平</strong>，最好能达到 ref。</p>
<p>下面是可以参考的例子：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br></pre></td><td class="code"><pre><code class="hljs sql"># 创建数据库表<br><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> t_emp<br>(<br>    emp_id     <span class="hljs-type">int</span> auto_increment <span class="hljs-keyword">primary</span> key,<br>    emp_name   <span class="hljs-type">char</span>(<span class="hljs-number">100</span>),<br>    emp_salary <span class="hljs-keyword">double</span>(<span class="hljs-number">10</span>, <span class="hljs-number">5</span>),<br>    dept_id    <span class="hljs-type">int</span><br>);<br><br><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> t_dept<br>(<br>    dept_id   <span class="hljs-type">int</span> auto_increment <span class="hljs-keyword">primary</span> key,<br>    dept_name <span class="hljs-type">char</span>(<span class="hljs-number">100</span>)<br>);<br><br># emp_id 主键索引<br># emp_name 唯一索引<br><span class="hljs-keyword">create</span> <span class="hljs-keyword">unique</span> index idx_emp_name_unique <span class="hljs-keyword">on</span> t_emp (emp_name);<br># dept_name 普通索引<br><span class="hljs-keyword">create</span> index idx_dept_name <span class="hljs-keyword">on</span> t_dept (dept_name);<br><br># 情况一：type 的值是 <span class="hljs-keyword">all</span><br># 原因：由于没有用到任何索引，所以执行全表扫描<br>explain<br><span class="hljs-keyword">select</span> emp_salary<br><span class="hljs-keyword">from</span> t_emp;<br><br># 情况二：type 的值是 index<br># 原因：查询的是建立了索引的字段，而且没有指定 <span class="hljs-keyword">where</span> 条件。<br># 在执行查询时扫描索引的整个 B<span class="hljs-operator">+</span>Tree<br>explain<br><span class="hljs-keyword">select</span> emp_id<br><span class="hljs-keyword">from</span> t_emp;<br><br>explain<br><span class="hljs-keyword">select</span> emp_name<br><span class="hljs-keyword">from</span> t_emp;<br><br>explain<br><span class="hljs-keyword">select</span> dept_name<br><span class="hljs-keyword">from</span> t_dept;<br><br># 情况三：type 的值是 <span class="hljs-keyword">range</span><br># 原因：在一定范围内访问索引 B<span class="hljs-operator">+</span>Tree<br><br># emp_salary 普通索引<br><span class="hljs-keyword">create</span> index idx_emp_salary <span class="hljs-keyword">on</span> t_emp (emp_salary);<br><br>explain<br><span class="hljs-keyword">select</span> emp_id, emp_name<br><span class="hljs-keyword">from</span> t_emp<br><span class="hljs-keyword">where</span> emp_salary <span class="hljs-keyword">between</span> <span class="hljs-number">1000</span> <span class="hljs-keyword">and</span> <span class="hljs-number">5000</span>;<br><br># 情况四：type 的值是 <span class="hljs-keyword">ref</span><br># 原因：通过普通的二级索引列与常量进行等值匹配时来查询<br>explain<br><span class="hljs-keyword">select</span> dept_name<br><span class="hljs-keyword">from</span> t_dept<br><span class="hljs-keyword">where</span> dept_name <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;研发部&#x27;</span>;<br><br># 情况五：对 t_dept 表的查询中，type 的值是 eq_ref<br># 原因：在进行关联查询时，被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问<br>explain<br><span class="hljs-keyword">select</span> emp_id, emp_name, emp_salary<br><span class="hljs-keyword">from</span> t_emp e<br>         <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> t_dept td <span class="hljs-keyword">on</span> e.dept_id <span class="hljs-operator">=</span> td.dept_id;<br><br># type 的值是 const<br># 原因：使用常量值查询一个唯一索引，返回唯一一条记录<br>explain<br><span class="hljs-keyword">select</span> emp_id, emp_name, emp_salary<br><span class="hljs-keyword">from</span> t_emp<br><span class="hljs-keyword">where</span> emp_name <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;aaa&#x27;</span>;<br><br></code></pre></td></tr></table></figure>



<h3 id="8、possible-keys-字段"><a href="#8、possible-keys-字段" class="headerlink" title="8、possible_keys 字段"></a>8、possible_keys 字段</h3><p>在查询中<strong>有可能</strong>会用到的索引列。如果没有任何索引显示 null。</p>
<h3 id="9、key-字段"><a href="#9、key-字段" class="headerlink" title="9、key 字段"></a>9、key 字段</h3><p>key 列显示 MySQL 实际决定使用的键（索引），包含在 possible_keys 中。</p>
<h3 id="10、key-len-字段-重要"><a href="#10、key-len-字段-重要" class="headerlink" title="10、key_len 字段[重要]"></a>10、key_len 字段[重要]</h3><p>key_len 表示索引使用的字节数，根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要，值越大索引的效果越好——因为值越大说明索引被利用的越充分。</p>
<p>字节数计算方式：</p>
<ul>
<li>索引对应字段类型：<ul>
<li>数值类型：最终结果就是数值类型字段宽度（我们关心的是字节数）本身<ul>
<li>int（11） ：我们关心的是int类型占四个字节而不是 11本身</li>
<li>double(10 , 5) :我们关心的是 double类型占 8 个字节， 而不是 10,5</li>
</ul>
</li>
<li>字符串类型：查看字符集类型<ul>
<li>UTF-8：需要给字段长度 × 3</li>
<li>GBK：需要给字段长度 × 2</li>
</ul>
</li>
<li>如果是 varchar 这样的变长字符串类型：再 + 2</li>
<li>如果是允许为空的字段：再 + 1</li>
</ul>
</li>
</ul>
<p>举例：customer_name 字段声明的类型是 varchar(200)，允许为空。</p>
<p>200 × 3 + 2 + 1 = 603</p>
<p>举例：</p>
<blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><code class="hljs sql"># 下面分析结果的 key_len 字段的值是 <span class="hljs-number">310</span>，我们来看看是怎么算出来的<br># 先看 emp_name<br># emp_name 是字符串类型，它的字段宽度是 <span class="hljs-number">100</span>，字符集是 UTF<span class="hljs-number">-8</span> 需要乘 <span class="hljs-number">3</span>，是定长字段不需要 <span class="hljs-operator">+</span><span class="hljs-number">2</span>，允许为空需要 <span class="hljs-operator">+</span><span class="hljs-number">1</span>，所以：<span class="hljs-number">100</span>×<span class="hljs-number">3</span><span class="hljs-operator">+</span><span class="hljs-number">1</span> <span class="hljs-operator">=</span> <span class="hljs-number">301</span><br># emp_salary 是数值类型，本身占 <span class="hljs-number">8</span> 个字节，允许为空需要 <span class="hljs-operator">+</span> <span class="hljs-number">1</span>，所以：<span class="hljs-number">8</span> <span class="hljs-operator">+</span> <span class="hljs-number">1</span> <span class="hljs-operator">=</span> <span class="hljs-number">9</span><br># 总和：<span class="hljs-number">301</span> <span class="hljs-operator">+</span> <span class="hljs-number">9</span> <span class="hljs-operator">=</span> <span class="hljs-number">310</span><br>explain <span class="hljs-keyword">select</span> emp_name,emp_salary <span class="hljs-keyword">from</span> t_emp<br><span class="hljs-keyword">where</span> emp_name <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;李四&#x27;</span> <span class="hljs-keyword">or</span> emp_salary <span class="hljs-operator">=</span> <span class="hljs-number">1000</span>;<br><br></code></pre></td></tr></table></figure>
</blockquote>
<h3 id="11、ref-字段"><a href="#11、ref-字段" class="headerlink" title="11、ref 字段"></a>11、ref 字段</h3><p>表示查询条件中，我们的索引列和谁去比较，是常量还是另一张表的字段。</p>
<h4 id="①const"><a href="#①const" class="headerlink" title="①const"></a>①const</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql">explain <span class="hljs-keyword">select</span> emp_id,emp_name,emp_salary <span class="hljs-keyword">from</span> t_emp <span class="hljs-keyword">where</span> emp_id<span class="hljs-operator">=</span><span class="hljs-number">5</span>;<br><br></code></pre></td></tr></table></figure>



<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL32.png?versionId=CAEQKRiBgMC2i5f7.xciIGEyMDE2M2Y3YWZmMzQ1NzM4YTI1YzUwZjU5YTkxNzQw" srcset="/img/loading.gif" lazyload alt="images"></p>
<h4 id="②某个字段"><a href="#②某个字段" class="headerlink" title="②某个字段"></a>②某个字段</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs sql">explain<br><span class="hljs-keyword">select</span> emp_id, emp_name, emp_salary<br><span class="hljs-keyword">from</span> t_emp e<br>         <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> t_dept d <span class="hljs-keyword">on</span> d.dept_id <span class="hljs-operator">=</span> e.dept_id<br><span class="hljs-keyword">where</span> emp_id <span class="hljs-operator">=</span> d.dept_id;<br><br></code></pre></td></tr></table></figure>



<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL33.png?versionId=CAEQKRiBgICzi5f7.xciIDk4ZDAwNTI3NGM5NjQ5ZDI5OGM0NTcwZGNiMGZiZmUy" srcset="/img/loading.gif" lazyload alt="images"></p>
<h3 id="12、rows-字段"><a href="#12、rows-字段" class="headerlink" title="12、rows 字段"></a>12、rows 字段</h3><p><strong>估算出结果集行数</strong>，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。从SQL优化的角度来说这个值越小越好。</p>
<h3 id="13、filtered-字段"><a href="#13、filtered-字段" class="headerlink" title="13、filtered 字段"></a>13、filtered 字段</h3><p>通过存储引擎从硬盘加载数据到服务层时，受限于内存空间，有可能只能加载一部分数据。filtered 字段显示的值是：已加载数据 / 全部数据 的百分比。只是不显示百分号。</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL34.png?versionId=CAEQKRiBgMC0i5f7.xciIDZlMDNjNmM5NDk1MDQ0OGI4M2UyZTI5MGJmMzdhMGI4" srcset="/img/loading.gif" lazyload alt="images"></p>
<h3 id="14、extra-字段"><a href="#14、extra-字段" class="headerlink" title="14、extra 字段"></a>14、extra 字段</h3><p>顾名思义，<code>Extra</code> 列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。我们可以通过这些额外信息来<code>更准确的理解MySQL到底将如何执行给定的查询语句</code>。MySQL提供的额外信息有好几十个，我们就不一个一个介绍了，所以我们只挑比较重要的额外信息介绍给大家。</p>
<p>下面画粗字体需要适当留意一下：</p>
<table>
<thead>
<tr>
<th>取值</th>
<th>含义</th>
</tr>
</thead>
<tbody><tr>
<td><strong>using where</strong></td>
<td>不用读取表中所有信息，仅通过索引就可以获取所需数据。 言外之意是 select 查询的字段都带有索引。 不管 select 查询多少个字段，这些字段都在索引中。</td>
</tr>
<tr>
<td><strong>Using temporary</strong></td>
<td>表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询</td>
</tr>
<tr>
<td><strong>Using filesort</strong></td>
<td>当语句中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序” 这里的文件指的是保存在硬盘上的文件。 之所以会用到硬盘，是因为如果查询的数据量太大，内存空间不够，需要在硬盘上完成排序。 如果确实是很大数据量在硬盘执行排序操作，那么速度会非常慢。</td>
</tr>
<tr>
<td>Using join buffer</td>
<td>buffer 指缓冲区，该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。 举例来说：where t_name like “%xxx%”，这个条件中的 t_name 字段没有加索引</td>
</tr>
<tr>
<td>Impossible where</td>
<td>where 子句中指定的条件查询不到数据的情况</td>
</tr>
<tr>
<td>Select tables optimized away</td>
<td>这个值表示目前的查询使用了索引，然后经过优化器优化之后，最终执行的是一个聚合函数，从而让最终的查询结果只返回一行</td>
</tr>
<tr>
<td>No tables used</td>
<td>查询语句中使用 from dual 或不含任何 from 子句</td>
</tr>
</tbody></table>
<h3 id="15、使用建议"><a href="#15、使用建议" class="headerlink" title="15、使用建议"></a>15、使用建议</h3><ul>
<li>EXPLAIN 不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况</li>
<li>EXPLAIN 不考虑各种 Cache</li>
<li>EXPLAIN 不能显示 MySQL 在执行查询时所作的优化工作</li>
<li>部分统计信息是估算的，并非精确值</li>
<li>EXPALIN 只能解释 SELECT 操作，其他操作要重写为 SELECT 后查看执行计划</li>
</ul>
<h1 id="第四节-索引失效"><a href="#第四节-索引失效" class="headerlink" title="第四节 索引失效"></a>第四节 索引失效</h1><h2 id="1、取消缓存功能"><a href="#1、取消缓存功能" class="headerlink" title="1、取消缓存功能"></a>1、取消缓存功能</h2><p>为了便于测试，避免 MySQL 内的缓存机制干扰分析结果，我们在分析 SQL 语句时取消 SQL 语句的缓存功能。</p>
<p>使用 <strong>SQL_NO_CACHE</strong> 关键字：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql">EXPLAIN <span class="hljs-keyword">SELECT</span> SQL_NO_CACHE <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> emp <span class="hljs-keyword">WHERE</span> emp.age<span class="hljs-operator">=</span><span class="hljs-number">30</span>;<br><br></code></pre></td></tr></table></figure>

<p>当然，实际开发时肯定是不会加的。</p>
<h2 id="2、最左原则-重要"><a href="#2、最左原则-重要" class="headerlink" title="2、最左原则 [重要]"></a>2、最左原则 [重要]</h2><h3 id="①有可能用到索引的子句"><a href="#①有可能用到索引的子句" class="headerlink" title="①有可能用到索引的子句"></a>①有可能用到索引的子句</h3><p>所有有过滤功能的子句都会将相关字段去和索引尝试匹配：</p>
<ul>
<li>ON 子句</li>
<li>WHERE 子句</li>
<li>GROUP BY 子句</li>
<li>HAVING 子句</li>
<li>LIMIT 子句</li>
</ul>
<h3 id="②最左原则"><a href="#②最左原则" class="headerlink" title="②最左原则"></a>②最左原则</h3><p>简单来说就是：MySQL 在决定是否要应用索引时，会对照 SQL 语句中要过滤的字段的顺序和索引中字段的顺序。那么具体是怎么对照的呢？请看下面的细节：</p>
<h4 id="1-创建索引"><a href="#1-创建索引" class="headerlink" title="[1]创建索引"></a>[1]创建索引</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> INDEX idx_age_deptid_name <span class="hljs-keyword">ON</span> emp(age, deptid, NAME);<br><br></code></pre></td></tr></table></figure>

<p>按照这个索引创建方式，索引中字段的顺序是：age、deptid、NAME</p>
<h4 id="2-仅查询-age"><a href="#2-仅查询-age" class="headerlink" title="[2]仅查询 age"></a>[2]仅查询 age</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><code class="hljs sql">EXPLAIN<br><span class="hljs-keyword">SELECT</span> SQL_NO_CACHE <span class="hljs-operator">*</span><br><span class="hljs-keyword">FROM</span> emp<br><span class="hljs-keyword">WHERE</span> emp.age <span class="hljs-operator">=</span> <span class="hljs-number">30</span>;<br><br></code></pre></td></tr></table></figure>

<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL35.png?versionId=CAEQKRiBgICxi5f7.xciIGVlYjdiMjg5YjExZTRmM2Y4NzhiNGJjMDY5M2E1MjU3" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>索引<strong>生效</strong>！</p>
<p>TIP</p>
<p>key_len 的值为什么是 5 ？</p>
<p>因为我们现在用到的索引字段就是 age 这一个字段，int 类型的字段占 4 个字节，可以为空再 +1。所以是 5。</p>
<h4 id="3-查询-age-和-deptId"><a href="#3-查询-age-和-deptId" class="headerlink" title="[3]查询 age 和 deptId"></a>[3]查询 age 和 deptId</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs sql">EXPLAIN<br><span class="hljs-keyword">SELECT</span> SQL_NO_CACHE <span class="hljs-operator">*</span><br><span class="hljs-keyword">FROM</span> emp<br><span class="hljs-keyword">WHERE</span> emp.age <span class="hljs-operator">=</span> <span class="hljs-number">30</span><br>  <span class="hljs-keyword">and</span> deptid <span class="hljs-operator">=</span> <span class="hljs-number">4</span>;<br><br></code></pre></td></tr></table></figure>

<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL36.png?versionId=CAEQKRiBgMCgi5f7.xciIDQxMzhmM2JlYWNmNTQ1NzM5NzMxOThjOTc1MmRmY2M2" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>索引<strong>生效</strong>！</p>
<h4 id="4-查询-age、deptId-和-name"><a href="#4-查询-age、deptId-和-name" class="headerlink" title="[4]查询 age、deptId 和 name"></a>[4]查询 age、deptId 和 name</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs sql">EXPLAIN<br><span class="hljs-keyword">SELECT</span> SQL_NO_CACHE <span class="hljs-operator">*</span><br><span class="hljs-keyword">FROM</span> emp<br><span class="hljs-keyword">WHERE</span> emp.age <span class="hljs-operator">=</span> <span class="hljs-number">30</span><br>  <span class="hljs-keyword">and</span> deptid <span class="hljs-operator">=</span> <span class="hljs-number">4</span><br>  <span class="hljs-keyword">AND</span> emp.name <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;abcd&#x27;</span>;<br><br></code></pre></td></tr></table></figure>



<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL37.png?versionId=CAEQKRiBgMCfi5f7.xciIDExNWFlODY0YjMwMDRkZmE4NDUyODgwNjUxZDBiMTA2" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>索引<strong>生效</strong>！</p>
<p>TIP</p>
<p><strong>单值索引和复合索引的选择</strong></p>
<p>在实际开发中，一个数据库表包含多个字段，其中有若干个字段有很大几率出现在 where 或其他有可能触发索引的子句中。那么我们倾向于创建『复合索引』，而不是『单值索引』。</p>
<p>因为一个复合索引能够涵盖到其中包含的每个字段；而给每一个字段分别创建单值索引会生成更多的索引表，增加切换、磁盘存储、I/O 等方面的开销。</p>
<h4 id="5-查询-deptId、name-和-age"><a href="#5-查询-deptId、name-和-age" class="headerlink" title="[5]查询 deptId、name 和 age"></a>[5]查询 deptId、name 和 age</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><code class="hljs sql">EXPLAIN<br><span class="hljs-keyword">SELECT</span> SQL_NO_CACHE <span class="hljs-operator">*</span><br><span class="hljs-keyword">FROM</span> emp<br><span class="hljs-keyword">WHERE</span><br>  deptid <span class="hljs-operator">=</span> <span class="hljs-number">4</span><br>  <span class="hljs-keyword">AND</span> emp.name <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;abcd&#x27;</span><br>  <span class="hljs-keyword">and</span>  emp.age <span class="hljs-operator">=</span> <span class="hljs-number">30</span><br>;<br><br></code></pre></td></tr></table></figure>



<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL38.png?versionId=CAEQKRiBgICei5f7.xciIDRmNzdiZjM3YTk5ZDRkNDJhMzMyMmU3NzgxNzAxOGYy" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>看上去索引是生效的，但是很明显顺序不一致，不满足最左原则，本来是不应该生效的：</p>
<ul>
<li>查询顺序：deptId、name、<strong>age</strong></li>
<li>索引顺序：<strong>age</strong>、deptid、NAME</li>
</ul>
<p>但是为什么索引生效了呢？其实原本是不应该生效的，但是此时是 MySQL 的 SQL 优化器调整了解析树，使查询字段符合了索引顺序，这才让索引生效了。</p>
<p>但是尽管优化器能够帮助我们进行调整，但是调整本身也需要付出代价，产生系统开销。所以我们开发时还是要尽量和索引中字段顺序一致。</p>
<h4 id="6-仅查询-deptId"><a href="#6-仅查询-deptId" class="headerlink" title="[6]仅查询 deptId"></a>[6]仅查询 deptId</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs sql">EXPLAIN<br><span class="hljs-keyword">SELECT</span> SQL_NO_CACHE <span class="hljs-operator">*</span><br><span class="hljs-keyword">FROM</span> emp<br><span class="hljs-keyword">WHERE</span> deptId <span class="hljs-operator">=</span> <span class="hljs-number">5</span><br>;<br><br></code></pre></td></tr></table></figure>



<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL39.png?versionId=CAEQKRiBgICci5f7.xciIGQzNzBmYmEwZmI4YzRmY2Q5ZDUxNWI4NjE5MTVjZWU1" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>索引<strong>没有生效</strong>！</p>
<p>但是很奇怪，deptId 这个字段明明是在索引中呀？这是因为本次查询没有满足最左原则。</p>
<p>在索引中，age 字段在最左边，现在查询的 deptId 作为第一个查询的字段不是 age，这就违背了<strong>最左原则</strong>。</p>
<p>TIP</p>
<p>为什么 MySQL 会如此执着于『最左』字段？</p>
<p>这是因为生成索引所在的 B+Tree 的时候，需要对索引值进行排序。那么如果我们指定的是联合索引，那么将涉及到多个字段的排序。例如：age、deptId、name这三个字段要排序的话，肯定优先根据 age 排序；然后在 age 值有相同数据时对 deptId 排序，以此类推。</p>
<p>所以我们在实际查询时，需要首先根据 age 字段在索引 B+Tree 中进行二分法查找。此时如果没有提供 age 字段，那将无法使用索引。</p>
<h4 id="7-查询-deptId-和-name"><a href="#7-查询-deptId-和-name" class="headerlink" title="[7]查询 deptId 和 name"></a>[7]查询 deptId 和 name</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs sql">EXPLAIN<br><span class="hljs-keyword">SELECT</span> SQL_NO_CACHE <span class="hljs-operator">*</span><br><span class="hljs-keyword">FROM</span> emp<br><span class="hljs-keyword">WHERE</span> deptId <span class="hljs-operator">=</span> <span class="hljs-number">5</span> <span class="hljs-keyword">and</span> name <span class="hljs-operator">=</span> &quot;aaa&quot;<br>;<br><br></code></pre></td></tr></table></figure>



<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL40.png?versionId=CAEQKRiBgICbi5f7.xciIDFhYTkxMDk4MGQzOTQ0MGJiZGJmMWU3MTc1M2Y2MTI0" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>索引<strong>没有生效</strong>！同样是因为违背了<strong>最左原则</strong>。</p>
<h4 id="8-查询-deptId-和-name-并按-age-排序"><a href="#8-查询-deptId-和-name-并按-age-排序" class="headerlink" title="[8]查询 deptId 和 name 并按 age 排序"></a>[8]查询 deptId 和 name 并按 age 排序</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs sql">EXPLAIN<br><span class="hljs-keyword">SELECT</span> SQL_NO_CACHE <span class="hljs-operator">*</span><br><span class="hljs-keyword">FROM</span> emp<br><span class="hljs-keyword">WHERE</span> deptId <span class="hljs-operator">=</span> <span class="hljs-number">5</span> <span class="hljs-keyword">and</span> name <span class="hljs-operator">=</span> &quot;aaa&quot; <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> age<br>;<br><br></code></pre></td></tr></table></figure>



<p>索引<strong>没有生效</strong>！这是因为 order by 没有过滤功能，不会触发索引。相当于查询过程中没有 age 字段参与。</p>
<h4 id="9-查询-age-和-name"><a href="#9-查询-age-和-name" class="headerlink" title="[9]查询 age 和 name"></a>[9]查询 age 和 name</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs sql">EXPLAIN<br><span class="hljs-keyword">SELECT</span> SQL_NO_CACHE <span class="hljs-operator">*</span><br><span class="hljs-keyword">FROM</span> emp<br><span class="hljs-keyword">WHERE</span> emp.age <span class="hljs-operator">=</span> <span class="hljs-number">30</span><br>  <span class="hljs-keyword">AND</span> emp.name <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;abcd&#x27;</span>;<br><br></code></pre></td></tr></table></figure>



<p>此时联合索引生效，但是 key_len 字段的值是 5，而不是 68，说明 name 字段并没有按照索引去查询。<strong>对 name 字段来说，索引没有生效</strong>。</p>
<h4 id="10-结论"><a href="#10-结论" class="headerlink" title="[10]结论"></a>[10]结论</h4><p>要遵循最左原则，查询字段中至少要有索引中的最左字段作为过滤条件存在。而且就最左原则本身来说，它要求索引最左字段在查询顺序中也最左。只不过只要最左字段出现，优化器会帮我们调整到查询顺序中的最左。而且还有一个要求是：中间不能断。中间一旦断开，后面的就都无法使用索引了。</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL41.png?versionId=CAEQKRiBgICfi5f7.xciIDgzNDIzZjhmNmYyOTRjODFhNjBlMTM1ZjZjYmYyZDcx" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>TIP</p>
<p>where 子句部分和最左原则对照，看是否生效的口诀：<strong>带头大哥不能死，中间兄弟不能断</strong></p>
<h2 id="3、索引失效的其他情况"><a href="#3、索引失效的其他情况" class="headerlink" title="3、索引失效的其他情况"></a>3、索引失效的其他情况</h2><h3 id="①在-where-子句条件中使用函数"><a href="#①在-where-子句条件中使用函数" class="headerlink" title="①在 where 子句条件中使用函数"></a>①在 where 子句条件中使用函数</h3><p>首先删除上例索引：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">drop</span> index idx_age_deptid_name <span class="hljs-keyword">on</span> emp;<br><br></code></pre></td></tr></table></figure>

<p>创建新索引：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">create</span> index idx_name <span class="hljs-keyword">on</span> emp(name);<br><br></code></pre></td></tr></table></figure>

<p>分析查询（left 函数表示取 name 字段的前三个字符）：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs sql">EXPLAIN<br><span class="hljs-keyword">SELECT</span> SQL_NO_CACHE <span class="hljs-operator">*</span><br><span class="hljs-keyword">FROM</span> emp<br><span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">left</span>(name, <span class="hljs-number">3</span>) <span class="hljs-operator">=</span> &quot;abc&quot;<br>;<br><br></code></pre></td></tr></table></figure>



<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL42.png?versionId=CAEQKRiBgMDjiZf7.xciIDdmOTgyMmMyZjdmMDRjNTE5MTU2ZmIxMTVhZGE3YjQ2" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>结论：使用了 left() 函数导致了索引失效。</p>
<h3 id="②范围查询"><a href="#②范围查询" class="headerlink" title="②范围查询"></a>②范围查询</h3><p>首先删除上例索引：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">drop</span> index idx_name <span class="hljs-keyword">on</span> emp;<br><br></code></pre></td></tr></table></figure>



<p>创建新索引：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> INDEX idx_age_deptid_name <span class="hljs-keyword">ON</span> emp(age, deptid, NAME);<br><br></code></pre></td></tr></table></figure>



<p>分析查询：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs sql">EXPLAIN<br><span class="hljs-keyword">SELECT</span> SQL_NO_CACHE <span class="hljs-operator">*</span><br><span class="hljs-keyword">FROM</span> emp<br><span class="hljs-keyword">WHERE</span> emp.age <span class="hljs-operator">=</span> <span class="hljs-number">30</span><br>  <span class="hljs-keyword">AND</span> emp.deptId <span class="hljs-operator">&gt;</span> <span class="hljs-number">20</span><br>  <span class="hljs-keyword">AND</span> emp.name <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;abc&#x27;</span>;<br><br></code></pre></td></tr></table></figure>



<p>分析结果：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL43.png?versionId=CAEQKRiBgIDhiZf7.xciIDliMGY1NTA2YTk4OTRjZWRhZjM3Nzk0ODEzNGRhZTNh" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>看起来仍然是生效的，但是我们再另外创建一个索引，把范围查询对应的 deptId 放在后面：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> INDEX idx_age_deptid_name_2 <span class="hljs-keyword">ON</span> emp(age, NAME, deptid);<br><br></code></pre></td></tr></table></figure>



<p>把查询顺序也改一下：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs sql">EXPLAIN<br><span class="hljs-keyword">SELECT</span> SQL_NO_CACHE <span class="hljs-operator">*</span><br><span class="hljs-keyword">FROM</span> emp<br><span class="hljs-keyword">WHERE</span> emp.age <span class="hljs-operator">=</span> <span class="hljs-number">30</span><br>  <span class="hljs-keyword">AND</span> emp.name <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;abc&#x27;</span><br>  <span class="hljs-keyword">AND</span> emp.deptId <span class="hljs-operator">&gt;</span> <span class="hljs-number">20</span>;<br><br></code></pre></td></tr></table></figure>



<p>分析结果：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL44.png?versionId=CAEQKRiBgIDfiZf7.xciIDZjNTMxMWYwNDVjNzRhMTA5NmI2NzZjYzVhZWExMDRl" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>请大家注意两点变化：</p>
<ul>
<li>变化1：MySQL 会优先选择新创建的索引 idx_age_deptid_name_2</li>
<li>变化2：key_len 数值变大了很多。</li>
</ul>
<p>分析：</p>
<ul>
<li>key_len 的计算角度：age 字段贡献了 5，deptId 字段贡献了 5，而 name 字段如果应用了索引，那么它应该贡献 63。但是最终 key_len 是 10，说明索引生效的字段是 age 和 deptId</li>
<li>type 的取值角度：range，说明查询的类型是范围查询，在当前 SQL 语句中 deptId 做的就是范围查询</li>
<li>解决方案角度：把 deptId 放在后面，三个字段的索引就都生效了。说明范围查询并不是祸害自己，而是祸害后面的查询条件。</li>
</ul>
<p>结论：</p>
<ul>
<li>注意点1：创建索引时应该把容易做范围查询的字段往后放</li>
<li>注意点2：实际编写 SQL 语句时，做范围查询的字段参照索引中的顺序，也是往后放</li>
</ul>
<p>进一步的问题：如果范围查询有多个呢？</p>
<p>创建索引：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> INDEX idx_age_deptid_name_3 <span class="hljs-keyword">ON</span> emp(NAME, age, deptid);<br><br></code></pre></td></tr></table></figure>



<p>执行分析：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs sql">EXPLAIN<br><span class="hljs-keyword">SELECT</span> SQL_NO_CACHE <span class="hljs-operator">*</span><br><span class="hljs-keyword">FROM</span> emp<br><span class="hljs-keyword">WHERE</span> emp.name <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;abc&#x27;</span><br>  <span class="hljs-keyword">AND</span> emp.age <span class="hljs-operator">&gt;</span> <span class="hljs-number">30</span><br>  <span class="hljs-keyword">AND</span> emp.deptId <span class="hljs-operator">&gt;</span> <span class="hljs-number">20</span>;<br><br></code></pre></td></tr></table></figure>



<p>查看分析结果，发现 key_len 是 68，68 = 63 + 5。说明生效的字段是 name、age，deptId 还是没有生效。这就说明范围查询即使放在后面也只有第一个生效。</p>
<h3 id="③不等于"><a href="#③不等于" class="headerlink" title="③不等于"></a>③不等于</h3><p>所有不等于操作都会导致索引失效：</p>
<ul>
<li>!=</li>
<li>&lt;&gt;</li>
<li>not</li>
</ul>
<p>测试的语句：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><code class="hljs sql"># 删除上例的索引<br><span class="hljs-keyword">drop</span> index idx_age_deptid_name <span class="hljs-keyword">on</span> emp;<br><span class="hljs-keyword">drop</span> index idx_age_deptid_name_2 <span class="hljs-keyword">on</span> emp;<br><br># 只针对 name 这一个字段创建一个新的索引<br><span class="hljs-keyword">create</span> index idx_name <span class="hljs-keyword">on</span> emp(name);<br><br># 分析对 name 查询的 <span class="hljs-keyword">SQL</span> 语句<br>explain <span class="hljs-keyword">select</span> sql_no_cache <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> emp <span class="hljs-keyword">where</span> name <span class="hljs-operator">!=</span> <span class="hljs-string">&#x27;aaa&#x27;</span>;<br>explain <span class="hljs-keyword">select</span> sql_no_cache <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> emp <span class="hljs-keyword">where</span> name <span class="hljs-operator">&lt;&gt;</span> <span class="hljs-string">&#x27;bbb&#x27;</span>;<br>explain <span class="hljs-keyword">select</span> sql_no_cache <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> emp <span class="hljs-keyword">where</span> name <span class="hljs-keyword">is</span> <span class="hljs-keyword">not</span> <span class="hljs-keyword">null</span>;<br><br></code></pre></td></tr></table></figure>



<p>分析结果：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL45.png?versionId=CAEQKRiBgIDdiZf7.xciIDc3YWUyOGFlNzBlNzRlNjQ5Njg4Y2U5OTI2ZWNjNzQ0" srcset="/img/loading.gif" lazyload alt="images"></p>
<h3 id="④like"><a href="#④like" class="headerlink" title="④like"></a>④like</h3><p>并不是所有 like 查询都会导致索引失效：</p>
<ul>
<li>不会导致索引失效的 like 用法：<ul>
<li>不使用 %</li>
<li>% 放在中间或后面</li>
</ul>
</li>
<li>会导致索引失效的 like 用法：% 放在前面</li>
</ul>
<h4 id="1-情况一：like-条件没有"><a href="#1-情况一：like-条件没有" class="headerlink" title="[1]情况一：like 条件没有 %"></a>[1]情况一：like 条件没有 %</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql">explain <span class="hljs-keyword">select</span> sql_no_cache <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> emp <span class="hljs-keyword">where</span> name <span class="hljs-keyword">like</span> <span class="hljs-string">&#x27;aaa&#x27;</span>;<br><br></code></pre></td></tr></table></figure>



<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL46.png?versionId=CAEQKRiDgMDMiZf7.xciIDMxNzFmOTU1MTlmNjQ0OGVhYTQzNmUwZTUzNWU4ZWU2" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>索引<strong>生效</strong>！</p>
<h4 id="2-情况二：like-条件把-放右边"><a href="#2-情况二：like-条件把-放右边" class="headerlink" title="[2]情况二：like 条件把 % 放右边"></a>[2]情况二：like 条件把 % 放右边</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql">explain <span class="hljs-keyword">select</span> sql_no_cache <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> emp <span class="hljs-keyword">where</span> name <span class="hljs-keyword">like</span> <span class="hljs-string">&#x27;aaa%&#x27;</span>;<br><br></code></pre></td></tr></table></figure>

<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL47.png?versionId=CAEQKRiBgMDMiZf7.xciIDIyYmJiZmJkN2E4NjRlOThhNTVhNmM4MTYzMmM0YjNi" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>索引<strong>生效</strong>！</p>
<h4 id="3-情况三：like-条件把-放中间"><a href="#3-情况三：like-条件把-放中间" class="headerlink" title="[3]情况三：like 条件把 % 放中间"></a>[3]情况三：like 条件把 % 放中间</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql">explain <span class="hljs-keyword">select</span> sql_no_cache <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> emp <span class="hljs-keyword">where</span> name <span class="hljs-keyword">like</span> <span class="hljs-string">&#x27;aaa%bbb&#x27;</span>;<br><br></code></pre></td></tr></table></figure>



<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL48.png?versionId=CAEQKRiBgIDLiZf7.xciIDViZDViZDAyMDAxODRjOGViZTkyZWVlMThjZTY1ZDk2" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>索引<strong>生效</strong>！</p>
<h4 id="4-情况四：like-条件把-放左边"><a href="#4-情况四：like-条件把-放左边" class="headerlink" title="[4]情况四：like 条件把 % 放左边"></a>[4]情况四：like 条件把 % 放左边</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql">explain <span class="hljs-keyword">select</span> sql_no_cache <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> emp <span class="hljs-keyword">where</span> name <span class="hljs-keyword">like</span> <span class="hljs-string">&#x27;%bbb&#x27;</span>;<br><br></code></pre></td></tr></table></figure>



<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL49.png?versionId=CAEQKRiBgIDJiZf7.xciIDZlYzhkMmNmMWNiZTQ1ZjU4YTI1NzQ0MTg3ZDgyZDgw" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>索引<strong>没有生效</strong>！</p>
<p>当然，左右都有 % 的情况和这里一样。</p>
<h3 id="⑤涉及类型转换"><a href="#⑤涉及类型转换" class="headerlink" title="⑤涉及类型转换"></a>⑤涉及类型转换</h3><p>所谓类型转换就是指：我们给查询条件传入的参数和原本的类型不一致。但是这种情况不是必然会导致索引失效。</p>
<ul>
<li>失效情况举例：让 char 类型的字段和 123 比较</li>
<li>有效情况举例：让 int 类型的字段和 ‘123’ 比较</li>
</ul>
<h4 id="1-失效情况"><a href="#1-失效情况" class="headerlink" title="[1]失效情况"></a>[1]失效情况</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs sql">explain <span class="hljs-keyword">select</span> sql_no_cache <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> emp<br><span class="hljs-keyword">where</span> name<span class="hljs-operator">=</span><span class="hljs-number">123</span>;<br><br></code></pre></td></tr></table></figure>

<p>分析结果：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL50.png?versionId=CAEQKRiBgIDIiZf7.xciIDllNzk5NzFmNTUxODQ1NTU4NjdhMmIxYzE4MDljMTE4" srcset="/img/loading.gif" lazyload alt="images"></p>
<h4 id="2-仍然有效的情况"><a href="#2-仍然有效的情况" class="headerlink" title="[2]仍然有效的情况"></a>[2]仍然有效的情况</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><code class="hljs sql">explain<br><span class="hljs-keyword">select</span> sql_no_cache <span class="hljs-operator">*</span><br><span class="hljs-keyword">from</span> emp<br><span class="hljs-keyword">where</span> age <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;123&#x27;</span>;<br><br></code></pre></td></tr></table></figure>



<p>分析结果：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL51.png?versionId=CAEQKRiBgMDIiZf7.xciIDE5MWNmNDYxMGYzYTQ3OTFiZmNmYTg5YWRlOGE3Njhk" srcset="/img/loading.gif" lazyload alt="images"></p>
<h2 id="4、小练习"><a href="#4、小练习" class="headerlink" title="4、小练习"></a>4、小练习</h2><p>假设目前我们有索引的情况是：index(a,b,c)</p>
<table>
<thead>
<tr>
<th>Where语句</th>
<th>索引是否被使用</th>
</tr>
</thead>
<tbody><tr>
<td>where a = 3</td>
<td>Y,使用到a</td>
</tr>
<tr>
<td>where a = 3 and b = 5</td>
<td>Y,使用到a，b</td>
</tr>
<tr>
<td>where a = 3 and b = 5 and c = 4</td>
<td>Y,使用到a,b,c</td>
</tr>
<tr>
<td>where b = 3 where b = 3 and c = 4 where c = 4</td>
<td>N，<strong>违背最左原则</strong></td>
</tr>
<tr>
<td>where a = 3 and c = 5</td>
<td>使用到a， 但是c不可以，<strong>b中间断了</strong></td>
</tr>
<tr>
<td>where a = 3 and b &gt; 4 and c = 5</td>
<td>使用到a和b， <strong>c不能用在范围之后</strong>，c 被 b 断给了</td>
</tr>
<tr>
<td>where a is null and b is <strong>not</strong> null</td>
<td>is null 支持索引 但是is not null 不支持,所以 a 可以使用索引,但是 b不可以使用</td>
</tr>
<tr>
<td>where a <strong>&lt;&gt;</strong> 3</td>
<td>不能使用索引</td>
</tr>
<tr>
<td>where abs(a) =3</td>
<td>不能使用索引，因为使用了<strong>函数</strong></td>
</tr>
<tr>
<td>where a = 3 and b like ‘kk%’ and c = 4</td>
<td>Y,使用到a,b,c。虽然用到了 like，但是左边是确定的。</td>
</tr>
<tr>
<td>where a = 3 and b like ‘%kk’ and c = 4</td>
<td>Y,只用到a, <strong>%不能在最左边</strong></td>
</tr>
<tr>
<td>where a = 3 and b like ‘%kk%’ and c = 4</td>
<td>Y,只用到a,<strong>%不能在最左边</strong></td>
</tr>
<tr>
<td>where a = 3 and b like ‘k%kk%’ and c = 4</td>
<td>Y,使用到a,b,c</td>
</tr>
</tbody></table>
<h2 id="5、小结"><a href="#5、小结" class="headerlink" title="5、小结"></a>5、小结</h2><ul>
<li>对于单键索引，尽量选择过滤性更好的索引（例如：手机号，邮件，身份证）。这样更容易过滤掉大量数据，为后面操作减轻负担。</li>
<li>在选择组合索引的时候，过滤性最好的字段在索引字段顺序中，位置越靠前越好。这样可以更早过滤掉大量数据。</li>
<li>选择组合索引时，尽量包含where中更多字段的索引。</li>
<li>组合索引出现范围查询时，尽量把这个字段放在索引次序的最后面</li>
<li>尽量避免造成索引失效的情况</li>
</ul>
<h1 id="第五节-关联查询优化"><a href="#第五节-关联查询优化" class="headerlink" title="第五节 关联查询优化"></a>第五节 关联查询优化</h1><h2 id="1、数据准备"><a href="#1、数据准备" class="headerlink" title="1、数据准备"></a>1、数据准备</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br></pre></td><td class="code"><pre><code class="hljs sql">#分类<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> IF <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">EXISTS</span> `class` (<br>`id` <span class="hljs-type">INT</span>(<span class="hljs-number">10</span>) UNSIGNED <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> AUTO_INCREMENT,<br>`card` <span class="hljs-type">INT</span>(<span class="hljs-number">10</span>) UNSIGNED <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span>,<br><span class="hljs-keyword">PRIMARY</span> KEY (`id`)<br>);<br>#图书<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> IF <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">EXISTS</span> `book` (<br>`bookid` <span class="hljs-type">INT</span>(<span class="hljs-number">10</span>) UNSIGNED <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> AUTO_INCREMENT,<br>`card` <span class="hljs-type">INT</span>(<span class="hljs-number">10</span>) UNSIGNED <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span>,<br><span class="hljs-keyword">PRIMARY</span> KEY (`bookid`)<br>);<br> <br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> class(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> class(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> class(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> class(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> class(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> class(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> class(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> class(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> class(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> class(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> class(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> class(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> class(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> class(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> class(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> class(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> class(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> class(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> class(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> class(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br> <br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-built_in">FLOOR</span>(<span class="hljs-number">1</span> <span class="hljs-operator">+</span> (RAND() <span class="hljs-operator">*</span> <span class="hljs-number">20</span>)));<br><br></code></pre></td></tr></table></figure>



<h2 id="2、left-join"><a href="#2、left-join" class="headerlink" title="2、left join"></a>2、left join</h2><h3 id="①测试"><a href="#①测试" class="headerlink" title="①测试"></a>①测试</h3><p>开始是没有加索引的情况。下面开始explain分析：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql">EXPLAIN <span class="hljs-keyword">SELECT</span> SQL_NO_CACHE <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> class <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span> book <span class="hljs-keyword">ON</span> class.card <span class="hljs-operator">=</span> book.card;<br><br></code></pre></td></tr></table></figure>



<p>分析结果：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL52.png?versionId=CAEQKRiCgMCSiJf7.xciIDg1N2E5NmZhODE2MDQwNjA5NWU5MWMzYjg0ZmQ5Njg0" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>添加索引优化</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> book <span class="hljs-keyword">ADD</span> INDEX Y (card); <br><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> class <span class="hljs-keyword">ADD</span> INDEX X (card); <br><br></code></pre></td></tr></table></figure>



<p>重新分析的结果：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL53.png?versionId=CAEQKRiBgMCRiJf7.xciIDkxZGJhN2JjZTU3YzQ1MThiNWJmZGJkNjJiZTczMzc3" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>看这个分析结果发现：在 class 表上添加的索引起的作用不大。</p>
<h3 id="③结论"><a href="#③结论" class="headerlink" title="③结论"></a>③结论</h3><ul>
<li><strong>小表驱动大表</strong><ul>
<li>小表：相对来说记录较少的表</li>
<li>大表：相对来说记录较多的表</li>
</ul>
</li>
<li>驱动方式识别<ul>
<li>left join：左边驱动右边（此时把小表放在左边）</li>
<li>right join：右边驱动左边（此时把小表放在右边）</li>
</ul>
</li>
<li>加索引的方式：通常建议在大表（被驱动）的表加索引，效率提升更明显。</li>
<li>原因：<ul>
<li>原因1：被驱动表加了索引之后，收益更大。从 ALL -&gt; ref</li>
<li>原因2：外连接首先读取驱动表的全部数据，被驱动只读取满足连接条件的数据。</li>
</ul>
</li>
</ul>
<h2 id="3、inner-join"><a href="#3、inner-join" class="headerlink" title="3、inner join"></a>3、inner join</h2><p>换成inner join（MySQL自动选择驱动表）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs sql"># 特意将 book 放在 <span class="hljs-keyword">from</span> 子句，去对 class 表做内连接<br>EXPLAIN<br><span class="hljs-keyword">SELECT</span> SQL_NO_CACHE <span class="hljs-operator">*</span><br><span class="hljs-keyword">FROM</span> book<br>         <span class="hljs-keyword">inner</span> <span class="hljs-keyword">JOIN</span> class <span class="hljs-keyword">ON</span> class.card <span class="hljs-operator">=</span> book.card;<br><br></code></pre></td></tr></table></figure>



<p>分析结果：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL54.png?versionId=CAEQKRiBgMCPiJf7.xciIGU4NTc3MjM1ZGIzNjRlOTNhMzI1Y2FjMGI3NGIyMTZj" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>MySQL 还是选择了 class 作为驱动表。</p>
<blockquote>
<p>需要给book表多添加记录，让这个表数据流有明显差异才有效果</p>
</blockquote>
<h2 id="4、小结"><a href="#4、小结" class="headerlink" title="4、小结"></a>4、小结</h2><ul>
<li>保证被驱动表的 join 字段被索引。join 字段就是作为连接条件的字段。</li>
<li>left join 时，选择小表作为驱动表（放左边），大表作为被驱动表（放右边）</li>
<li>inner join 时，mysql 会自动将小结果集的表选为驱动表。</li>
<li>子查询尽量不要放在被驱动表，衍生表建不了索引</li>
<li>能够直接多表关联的尽量直接关联，不用子查询</li>
</ul>
<h1 id="第六节-子查询优化"><a href="#第六节-子查询优化" class="headerlink" title="第六节 子查询优化"></a>第六节 子查询优化</h1><h2 id="1、结论"><a href="#1、结论" class="headerlink" title="1、结论"></a>1、结论</h2><p>在实际开发中，能够不用子查询就尽量不用。</p>
<h2 id="2、实际测试"><a href="#2、实际测试" class="headerlink" title="2、实际测试"></a>2、实际测试</h2><h3 id="①测试子查询效果"><a href="#①测试子查询效果" class="headerlink" title="①测试子查询效果"></a>①测试子查询效果</h3><p>添加索引：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">create</span> index idx_ceo <span class="hljs-keyword">on</span> dept(ceo);<br><br></code></pre></td></tr></table></figure>

<p>分析语句：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><code class="hljs sql">explain <span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span><br><span class="hljs-keyword">FROM</span> emp a<br><span class="hljs-keyword">WHERE</span> a.id <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">IN</span><br>      (<span class="hljs-keyword">SELECT</span> b.ceo <span class="hljs-keyword">FROM</span> dept b <span class="hljs-keyword">WHERE</span> b.ceo <span class="hljs-keyword">IS</span> <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span>);<br><br></code></pre></td></tr></table></figure>

<p>分析结果：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL55.png?versionId=CAEQKRiBgICLiJf7.xciIGUzZGRlZDI4NGRjMjQ1NjM5NDEwN2IwODdjZGU3YjMw" srcset="/img/loading.gif" lazyload alt="images"></p>
<h3 id="②测试关联查询效果"><a href="#②测试关联查询效果" class="headerlink" title="②测试关联查询效果"></a>②测试关联查询效果</h3><p>分析语句：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><code class="hljs sql">explain <span class="hljs-keyword">SELECT</span> a.<span class="hljs-operator">*</span><br><span class="hljs-keyword">FROM</span> emp a<br>         <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span> dept b<br>                   <span class="hljs-keyword">ON</span> a.id <span class="hljs-operator">=</span> b.ceo;<br><br></code></pre></td></tr></table></figure>



<p>分析结果：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL56.png?versionId=CAEQKRiBgID3h5f7.xciIGQxZWY2ZmEwZWJhMDRhNzU5YzAyMjc1ZmNhYzU3MmNh" srcset="/img/loading.gif" lazyload alt="images"></p>
<h3 id="③综合对比"><a href="#③综合对比" class="headerlink" title="③综合对比"></a>③综合对比</h3><ul>
<li>从执行顺序来说：关联查询的 id(1,1) 比 子查询的 id(1,2) 要好</li>
<li>从查询的类型来说：ref 比 index 要好两个级别</li>
</ul>
<h1 id="第七节-排序分组优化"><a href="#第七节-排序分组优化" class="headerlink" title="第七节 排序分组优化"></a>第七节 排序分组优化</h1><h2 id="1、排序"><a href="#1、排序" class="headerlink" title="1、排序"></a>1、排序</h2><h3 id="①结论"><a href="#①结论" class="headerlink" title="①结论"></a>①结论</h3><ul>
<li>order by 子句需要配合 limit 子句才能让索引生效。</li>
<li>如果涉及多个字段的排序，那么这些字段排序的方向要一致（要升序都升序，要降序都降序），否则索引失效。</li>
</ul>
<h3 id="②测试基本情况"><a href="#②测试基本情况" class="headerlink" title="②测试基本情况"></a>②测试基本情况</h3><p>索引情况：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL57.png?versionId=CAEQKRiBgMDyh5f7.xciIDJhNmNjMTJhMjY5NTQzODc5ZWVkNTVhMDJjMDEzNGUx" srcset="/img/loading.gif" lazyload alt="images"></p>
<h4 id="1-只有-order-by-的情况"><a href="#1-只有-order-by-的情况" class="headerlink" title="[1]只有 order by 的情况"></a>[1]只有 order by 的情况</h4><p>分析语句：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql">EXPLAIN  <span class="hljs-keyword">SELECT</span> SQL_NO_CACHE <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> emp <span class="hljs-keyword">ORDER</span>  <span class="hljs-keyword">BY</span> age;<br><br></code></pre></td></tr></table></figure>

<p>分析结果：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL58.png?versionId=CAEQKRiBgMD5h5f7.xciIGI5NGE1ZDlkNWI1NjQzMzdhNzFmOTI0NzdjNWY1OTYx" srcset="/img/loading.gif" lazyload alt="images"></p>
<h4 id="2-配合-limit-子句的情况"><a href="#2-配合-limit-子句的情况" class="headerlink" title="[2]配合 limit 子句的情况"></a>[2]配合 limit 子句的情况</h4><p>分析语句：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql">EXPLAIN  <span class="hljs-keyword">SELECT</span> SQL_NO_CACHE <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> emp <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> age limit <span class="hljs-number">10</span>;<br><br></code></pre></td></tr></table></figure>

<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL59.png?versionId=CAEQKRiBgMD3h5f7.xciIDA0OTI4ZjQxOTJmMzRlOTM5MmViOTg5NGEwNTNmNTM1" srcset="/img/loading.gif" lazyload alt="images"></p>
<h3 id="③测试排序方向的影响"><a href="#③测试排序方向的影响" class="headerlink" title="③测试排序方向的影响"></a>③测试排序方向的影响</h3><p>删除上例索引：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">drop</span> index idx_age <span class="hljs-keyword">on</span> emp;<br><br></code></pre></td></tr></table></figure>

<p>创建新索引：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">create</span> index idx_age_deptId <span class="hljs-keyword">on</span> emp(age, deptId);<br><br></code></pre></td></tr></table></figure>

<p>分析语句：排序方向一致的情况</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql">EXPLAIN  <span class="hljs-keyword">SELECT</span> SQL_NO_CACHE <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> emp <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> age <span class="hljs-keyword">desc</span>,deptId <span class="hljs-keyword">desc</span> limit <span class="hljs-number">10</span>;<br><br></code></pre></td></tr></table></figure>

<p>分析结果：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL60.png?versionId=CAEQKRiCgMD5h5f7.xciIGJlZjJiMjM5ZGIxODQ0NzVhMjhlYzg4MDIwMWY3Zjk5" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>分析语句：排序方向不一致的情况</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql">EXPLAIN  <span class="hljs-keyword">SELECT</span> SQL_NO_CACHE <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> emp <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> age <span class="hljs-keyword">desc</span>,deptId <span class="hljs-keyword">asc</span> limit <span class="hljs-number">10</span>;<br><br></code></pre></td></tr></table></figure>

<p>分析结果：</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL61.png?versionId=CAEQKRiBgIDth5f7.xciIDg0NmNiNmRiNGMxMDQwNmU4MTllNThkNWNlMGEzNjhl" srcset="/img/loading.gif" lazyload alt="images"></p>
<h2 id="2、双路排序和单路排序"><a href="#2、双路排序和单路排序" class="headerlink" title="2、双路排序和单路排序"></a>2、双路排序和单路排序</h2><h3 id="①filesort"><a href="#①filesort" class="headerlink" title="①filesort"></a>①filesort</h3><p>如果 order by 指定的字段没有建立索引，此时 MySQL 就无法在内存完成排序了，而是执行 filesort——也就是操作硬盘完成排序。</p>
<p>执行 filesort 又分两种不同情况：</p>
<ul>
<li>双路排序：<ul>
<li>特征：两批 I/O</li>
<li>对应的 MySQL 版本：4.1 之前</li>
<li>大致工作机制：<ul>
<li>第一次读取硬盘：读取『行指针』以及『order by 子句指定的字段』</li>
<li>排序：对已读取的『order by 子句指定的字段』进行排序</li>
<li>第二次读取硬盘：根据『行指针』读取 select 子句中指定的其他字段</li>
</ul>
</li>
<li>I/O方式：随机 I/O</li>
</ul>
</li>
<li>单路排序：<ul>
<li>特征：一批 I/O</li>
<li>对应的 MySQL 版本：4.1 之后</li>
<li>大致工作机制：<ul>
<li>读取硬盘：读取 select 子句指定的所有列</li>
<li>排序：按照 order by 列在 buffer 中对它们进行排序</li>
</ul>
</li>
<li>I/O方式：顺序I/O</li>
</ul>
</li>
</ul>
<h3 id="②单路排序需要注意的问题"><a href="#②单路排序需要注意的问题" class="headerlink" title="②单路排序需要注意的问题"></a>②单路排序需要注意的问题</h3><p>单路排序在内存的缓冲区中执行排序，所以需要更大的内存空间。我们管这个缓冲区叫：sort_buffer。此时需要注意：如果为了排序而取出的数据体积大于 sort_buffer，这就会导致每次只能取 sort_buffer 容量大小的数据。所以这种情况下，数据的加载和排序是分段完成的。在这个过程中，MySQL 会创建临时文件，最后再把每段数据合并到一起。</p>
<p>所以 sort_buffer 容量太小时会导致排序操作产生多次 I/O。单路本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失。</p>
<h3 id="③单路排序的优化"><a href="#③单路排序的优化" class="headerlink" title="③单路排序的优化"></a>③单路排序的优化</h3><p>调整下面的三个参数：</p>
<table>
<thead>
<tr>
<th>参数名称</th>
<th>参数含义</th>
<th>调整建议</th>
</tr>
</thead>
<tbody><tr>
<td>sort_buffer_size</td>
<td>单路排序缓冲区的大小</td>
<td>适当增大</td>
</tr>
<tr>
<td>max_length_for_sort_data</td>
<td>select 子句要查询的所有字段的总宽度和该参数比较： 大于该参数：使用双路排序 小于等于该参数且排序字段不是TEXT、BLOB类型：使用单路排序</td>
<td>适当增大</td>
</tr>
<tr>
<td>select 子句中查询的字段</td>
<td></td>
<td>尽量减少</td>
</tr>
</tbody></table>
<p>TIP</p>
<p>对 sort_buffer_size 的补充说明：</p>
<p>不管用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高，因为这个参数是针对每个进程（connection）的 1M-8M之间调整。 MySQL5.7，InnoDB 存储引擎默认值是 1048576 字节，1MB。</p>
<p>对 max_length_for_sort_data 的补充说明：</p>
<p>max_length_for_sort_data 不能设的太高，否则数据总容量超出 sort_buffer_size 的概率就增大。明显症状是高的磁盘 I/O 活动和低的处理器使用率。建议设置在 1024-8192 字节之间。</p>
<p>最终目标：</p>
<ul>
<li>尽量能够基于索引执行排序</li>
<li>如果确实要执行 filesort，那么尽量使用单路排序，而且尽量让单路排序只做一次 I/O。</li>
</ul>
<h3 id="④select-会影响排序"><a href="#④select-会影响排序" class="headerlink" title="④select * 会影响排序"></a>④select * 会影响排序</h3><p>在实际业务功能开发过程中，禁止在 select 子句中使用 * 号代表全部字段。如果确实需要查询全部字段，那就把全部字段都写明。其实这个时候更要注意的是：是不是真的要查全部字段。</p>
<p>具体从 SQL 优化的角度来说，select * 会导致我们加载很多没有创建索引的字段到内存中，增加了数据体积超过 sort_buffer_size 的风险。<strong>有可能会导致单路排序变成双路排序，性能下降。</strong></p>
<h2 id="3、GROUP-BY优化"><a href="#3、GROUP-BY优化" class="headerlink" title="3、GROUP BY优化"></a>3、GROUP BY优化</h2><p>Group by 分组优化原则如下：</p>
<ul>
<li>group by 先排序再分组，同样遵照<strong>最左原则</strong></li>
<li>当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置</li>
<li>where 高于 having，能写在 where 限定的条件就不要写在 having 中了</li>
</ul>
<p>举个例子帮助大家理解：</p>
<ul>
<li>假设有 100W 条记录待筛选</li>
<li>有一个 X 条件能够过滤掉 90W 条<ul>
<li>X 条件用在 where 子句：后续 GROUP BY 操作针对 10W 条数据操作</li>
<li>X 条件用在 having子句：后续 GROUP BY 操作还是针对原来的 100W 条数据操作</li>
</ul>
</li>
</ul>
<p>所以在整个 SQL 查询语句中，能够将数据过滤掉的条件在不影响查询结果的前提下都要尽早使用，<strong>尽早过滤数据</strong>，缩小要操作的数据量，让后续操作减轻负担。</p>
<p>关闭 ONLY_FULL_GROUP_BY 模式：</p>
<ul>
<li>查看当前 SQL 模式</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs SQL"><span class="hljs-keyword">select</span> @<span class="hljs-variable">@GLOBAL</span>.sql_mode;<br><br></code></pre></td></tr></table></figure>

<ul>
<li><p>关闭 ONLY_FULL_GROUP_BY 模式</p>
<p>修改 /etc/my.cnf 配置文件，在配置文件末尾增加一行：</p>
</li>
</ul>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs Bash">sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION<br><br></code></pre></td></tr></table></figure>

<ul>
<li>重启 MySQL 服务</li>
<li>查看修改完成后的效果</li>
</ul>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs Bash">select @@GLOBAL.sql_mode;<br><br></code></pre></td></tr></table></figure>

<p>TIP 示例</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><code class="hljs mysql">关闭 ONLY_FULL_GROUP_BY 模式<br><br>查看当前 SQL 模式<br>mysql&gt; select @@GLOBAL.sql_mode;<br>+-------------------------------------------------------------------------------------------------------------------------------------------+ | @@GLOBAL.sql_mode | +-------------------------------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)<br><br>关闭 ONLY_FULL_GROUP_BY 模式<br>修改 /etc/my.cnf 配置文件，在配置文件末尾增加一行：<br><br>sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION<br>重启 MySQL 服务<br>查看修改完成后的效果<br>mysql&gt; select @@GLOBAL.sql_mode; +------------------------------------------------------------------------------------------------------------------------+ | @@GLOBAL.sql_mode | +------------------------------------------------------------------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)<br><br>mysql&gt; use db_hr_sys; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A<br><br>Database changed mysql&gt; mysql&gt; select * from emp group by deptid limit 0,10; +-------+--------+--------+------+--------+ | id | empno | name | age | deptId | +-------+--------+--------+------+--------+ | 10893 | 110893 | MJTDjl | 41 | 1 | | 523 | 100523 | VOizXo | 41 | 2 | | 7118 | 107118 | VthHLL | 39 | 3 | | 14788 | 114788 | PTasQX | 37 | 4 | | 16297 | 116297 | gLpozF | 41 | 5 | | 7597 | 107597 | ZItShh | 35 | 6 | | 817 | 100817 | mxzhmN | 31 | 7 | | 1673 | 101673 | demYgL | 34 | 8 | | 30032 | 130032 | fYDUPn | 50 | 9 | | 1169 | 101169 | wjFANm | 49 | 10 | +-------+--------+--------+------+--------+ 10 rows in set (0.53 sec)<br><br></code></pre></td></tr></table></figure>

<h2 id="视图"><a href="#视图" class="headerlink" title="视图"></a>视图</h2><p>实际开发时，现在越来越多『长、难、复杂』SQL。这种 SQL 语句编写、维护较为困难。所以我们可以将这一的 SQL 语句创建为『视图』，这个视图生成了一张虚拟的表。下次再有需要时，执行这个视图即可得到相同的结果。</p>
<p><img src="https://lllong.oss-cn-shenzhen.aliyuncs.com/Mysql/Mysql%E9%AB%98%E7%BA%A7/MySQL23.png?versionId=CAEQKRiBgICLjZf7.xciIDQxYzBkMzMyM2ZlNjQ2YmNhNzQ5OGMyZGQ1NzA5OGVj" srcset="/img/loading.gif" lazyload alt="images"></p>
<p>视图是将一段查询 SQL 封装为一个虚拟的表。 这个虚拟表只保存了 SQL 逻辑，不会保存任何查询结果。</p>
<p><strong>主要作用:</strong></p>
<ul>
<li>封装复杂sql语句，提高复用性</li>
<li>逻辑放在数据库上面，更新不需要发布程序，面对频繁的需求变更更灵活</li>
</ul>
<p><strong>常用场景:</strong></p>
<ul>
<li>共用查询结果</li>
<li>报表</li>
</ul>
<p><strong>语法:</strong></p>
<p>创建</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">VIEW</span> view_name <span class="hljs-keyword">AS</span> <span class="hljs-keyword">SELECT</span> column_name(s) <span class="hljs-keyword">FROM</span> table_name  <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">condition</span>  <br><br></code></pre></td></tr></table></figure>

<p>使用</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><code class="hljs sql">#查询<br><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> view_name <br>#更新<br><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">OR</span> REPLACE <span class="hljs-keyword">VIEW</span> view_name <br><span class="hljs-keyword">AS</span> <span class="hljs-keyword">SELECT</span> column_name(s) <span class="hljs-keyword">FROM</span> table_name <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">condition</span><br><br></code></pre></td></tr></table></figure>

<h1 id><a href="#" class="headerlink" title></a></h1>
            </div>
            <hr>
            <div>
              <div class="post-metas mb-3">
                
                  <div class="post-meta mr-3">
                    <i class="iconfont icon-category"></i>
                    
                      <a class="hover-with-bg" href="/categories/mysql/">mysql</a>
                    
                  </div>
                
                
                  <div class="post-meta">
                    <i class="iconfont icon-tags"></i>
                    
                      <a class="hover-with-bg" href="/tags/mysql/">mysql</a>
                    
                  </div>
                
              </div>
              
                <p class="note note-warning">
                  
                    本博客目前大部分文章都是参考尚硅谷或者马士兵教育的学习资料！<a href="http://www.atguigu.com/" rel="nofollow noopener"官网地址！</a> 
                  
                </p>
              
              
                <div class="post-prevnext">
                  <article class="post-prev col-6">
                    
                    
                      <a href="/2022/03/14/MySQL-%E5%A4%8D%E5%88%B6%E6%9E%B6%E6%9E%84/">
                        <i class="iconfont icon-arrowleft"></i>
                        <span class="hidden-mobile">MySQL-复制架构</span>
                        <span class="visible-mobile">上一篇</span>
                      </a>
                    
                  </article>
                  <article class="post-next col-6">
                    
                    
                      <a href="/2022/03/14/MySQL-%E7%B4%A2%E5%BC%95%E5%8E%9F%E7%90%86/">
                        <span class="hidden-mobile">MySQL-索引原理</span>
                        <span class="visible-mobile">下一篇</span>
                        <i class="iconfont icon-arrowright"></i>
                      </a>
                    
                  </article>
                </div>
              
            </div>

            
              <!-- Comments -->
              <article class="comments" id="comments" lazyload>
                
                  
                
                

              </article>
            
          </article>
        </div>
      </div>
    </div>
    
      <div class="d-none d-lg-block col-lg-2 toc-container" id="toc-ctn">
        <div id="toc">
  <p class="toc-header"><i class="iconfont icon-list"></i>&nbsp;目录</p>
  <div class="toc-body" id="toc-body"></div>
</div>

      </div>
    
  </div>
</div>

<!-- Custom -->


    

    
      <a id="scroll-top-button" aria-label="TOP" href="#" role="button">
        <i class="iconfont icon-arrowup" aria-hidden="true"></i>
      </a>
    

    
      <div class="modal fade" id="modalSearch" tabindex="-1" role="dialog" aria-labelledby="ModalLabel"
     aria-hidden="true">
  <div class="modal-dialog modal-dialog-scrollable modal-lg" role="document">
    <div class="modal-content">
      <div class="modal-header text-center">
        <h4 class="modal-title w-100 font-weight-bold">搜索</h4>
        <button type="button" id="local-search-close" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body mx-3">
        <div class="md-form mb-5">
          <input type="text" id="local-search-input" class="form-control validate">
          <label data-error="x" data-success="v"
                 for="local-search-input">关键词</label>
        </div>
        <div class="list-group" id="local-search-result"></div>
      </div>
    </div>
  </div>
</div>
    

    
  </main>

  <footer class="text-center mt-5 py-3">

  <div class="footer-content">
     <a href="https://hexo.io" target="_blank" rel="nofollow noopener"><span>Hexo</span></a> <i class="iconfont icon-love"></i> <a href="https://github.com/fluid-dev/hexo-theme-fluid" target="_blank" rel="nofollow noopener"><span>Fluid</span></a> 
	<!--《添加网站运行时间 -->
<br/>

<span id="timeDate">载入天数...</span><span id="times">载入时分秒...</span>
<script>
var now = new Date(); 

function createtime() {
    //此处修改你的建站时间或者网站上线时间
    var grt = new Date('11/02/2021 21:39:00');
    now.setTime(now.getTime() + 250);
    days = (now - grt) / 1000 / 60 / 60 / 24;

    dnum = Math.floor(days);
    hours = (now - grt) / 1000 / 60 / 60 - (24 * dnum);
    hnum = Math.floor(hours);
    if (String(hnum).length == 1) {
        hnum = "0" + hnum;
    }
    minutes = (now - grt) / 1000 / 60 - (24 * 60 * dnum) - (60 * hnum);
    mnum = Math.floor(minutes);
    if (String(mnum).length == 1) {
        mnum = "0" + mnum;
    }
    seconds = (now - grt) / 1000 - (24 * 60 * 60 * dnum) - (60 * 60 * hnum) - (60 * mnum);
    snum = Math.round(seconds);
    if (String(snum).length == 1) {
        snum = "0" + snum;
    }
    document.getElementById("timeDate").innerHTML = " 本站已各种夹缝中安全运行 " + dnum + " 天 ";
    document.getElementById("times").innerHTML = hnum + " 小时 " + mnum + " 分 " + snum + " 秒";
}
setInterval("createtime()", 250);
</script>

<!-- 添加网站运行时间》-->
  </div>
  
  <div class="statistics">
    
    

    
      
        <!-- 不蒜子统计PV -->
        <span id="busuanzi_container_site_pv" style="display: none">
            总访问量 
            <span id="busuanzi_value_site_pv"></span>
             次
          </span>
      
      
        <!-- 不蒜子统计UV -->
        <span id="busuanzi_container_site_uv" style="display: none">
            总访客数 
            <span id="busuanzi_value_site_uv"></span>
             人
          </span>
      
    
  </div>


  

  
</footer>


  <!-- SCRIPTS -->
  
  <script  src="https://cdn.jsdelivr.net/npm/nprogress@0/nprogress.min.js" ></script>
  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/nprogress@0/nprogress.min.css" />

  <script>
    NProgress.configure({"showSpinner":false,"trickleSpeed":100})
    NProgress.start()
    window.addEventListener('load', function() {
      NProgress.done();
    })
  </script>


<script  src="https://cdn.jsdelivr.net/npm/jquery@3/dist/jquery.min.js" ></script>
<script  src="https://cdn.jsdelivr.net/npm/bootstrap@4/dist/js/bootstrap.min.js" ></script>
<script  src="/js/events.js" ></script>
<script  src="/js/plugins.js" ></script>

<!-- Plugins -->


  <script  src="/js/local-search.js" ></script>



  
    <script  src="/js/img-lazyload.js" ></script>
  



  



  
    <script  src="https://cdn.jsdelivr.net/npm/tocbot@4/dist/tocbot.min.js" ></script>
  
  
    <script  src="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3/dist/jquery.fancybox.min.js" ></script>
  
  
    <script  src="https://cdn.jsdelivr.net/npm/anchor-js@4/anchor.min.js" ></script>
  
  
    <script defer src="https://cdn.jsdelivr.net/npm/clipboard@2/dist/clipboard.min.js" ></script>
  



  <script defer src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js" ></script>




  <script  src="https://cdn.jsdelivr.net/npm/typed.js@2/lib/typed.min.js" ></script>
  <script>
    (function (window, document) {
      var typing = Fluid.plugins.typing;
      var title = document.getElementById('subtitle').title;
      
      typing(title)
      
    })(window, document);
  </script>















<!-- 主题的启动项 保持在最底部 -->
<script  src="/js/boot.js" ></script>


</body>
</html>
